6 Replies Latest reply on Jul 12, 2018 5:57 AM by Arthur Valle

    Calculating how many times a value repeats and checking previous values

     Arthur Valle

      Hey guys,

       

      i have a chart kinda like this:

       

      id1id2
      type_1
      num_2
      46329999A1
      46329999A1
      46329999B1
      555352C2
      626321D3
      6269924D3

       

      num_2 is the "master" value, it's unique and never changes, so I should use it to identify a record (I use this concept specially in the "2nd need")

       

      Unfortunatetly I can't share the actual chart, 'cause it's confidential. It might be important to say that it's a huge database, with over 100 million records. I've been looking for the solution to my problems for a week now, but couldn't find the right way to these things I need to get. Here are my needs:

       

      1st: I gotta filter the records by the num_2 column. I gotta get only the records which have a value of num_2 that is present in more than 1 record. I may have a calculated field, whatever. I just need to be able to know how much records have as value of the num_2 column a value that is repeated and also know how many times those values (of the num_2 column) repeat themselves (I do not need how many times each numbers repeat, but as a total). Keep in mind i have 100 million rows and all these values in either columns are pretty huge and vary a lot.

      Filtering it like I told you I need, I should get the same chart but whitout the fourth row.

       

       

      2nd: Nevertheless I need to know when a record keeps the num_2 (so that means it's actually the same thing), but had its id1 or id2 changed (like the last two rows). I gotta count how many times this happens in my database.

       

      Thanks in advance!

        • 1. Re: Calculating how many times a value repeats and checking previous values
          Michael Hesser

          Hi Arthur;

           

          You should be able to do this with a LOD expression, but without a better understanding of your database, it's difficult for me to give you precise instructions.

           

          Are you asking for the total number of occurrences of a value (num_2) within a single dimension?

          Are you asking for the total number of occurrences of a value (num_2) withing multiple dimensions?

           

          It would be helpful if you could show a small example with result (counts).

           

          Thank you!

          • 2. Re: Calculating how many times a value repeats and checking previous values
             Arthur Valle

            Hi, Miachael! Thanks for the answer!

             

            I actually was trying to use the fixed function. But I still don't trust the output I'm getting. Regarding your questions, I'm asking for the number of type_1 values attached to the same num_2 value.

             

            Lemme get a little bit more real with these data. I have the "num_2" column which is unique and allows me to identify what relates to the same "act" irl. The point is: if somethings happen irl with an already registered "act" (an existing num_2) I'll have a new record in my tableau server database which may or may not have a different type_1 value attached to. All I want is to be able to analyze exactly the records that show me something happened. In other words, if I have only one record with a num_2 value, it actually doesn't matter to this analysis we're looking foward to do. Therefore, I need to get rid of this "normal" cases. Not only do I have to be able to filter these normal cases off, but I gotta know how many times num_2 values repeat at all. That would mean like: I get all those rows with a num_2 value which appear in other rows (basically cutting off the ones that have an unique num_2 value) and count them. But also, - the thing that makes me think LOD functions may be useful here - I need to know how many times "something usually happens". How many records are there with the same num_2 value. I understand it may be kinda tricky and I don't actually know what's the best information i can get, since, as i said, this database is huge and it won't be reasoanable to like, get a graph of how many times each num_2 repeated (i have something close to 40 million num_2 values).

             

            Does that make it any more clear? I actually didn't get what you mean with "It would be helpful if you could show a small example with result (counts)."

            I'm sorry 

             

            Unfortunatetly I can't get you any prints or something like that, they're a very delicate data about huge companies and may affect whole countries if leaked. It's a really big database with different types of information. But if there's something specifically you need and you still think it's not a problem for me to send it, just tell me exactly what you need that I can try to figure someway out.

            • 3. Re: Calculating how many times a value repeats and checking previous values
               Arthur Valle

              Oh, and don't forget the second need. I gotta compare the id's whitin the num_2 values too. I mean, compare the id1 of all the records with 1 as num_2 value, as example. I gotta get how many (if any) times within all records with the same num_2, the id1 or id2 values changed.

               

              That may actually mean i gotta compare multiple dimensions within the num_2 value (if i got your question right)

              • 4. Re: Calculating how many times a value repeats and checking previous values
                 Arthur Valle

                I just tried the following calculated field to get the number of type_1 values distinctively within a num_2 value :

                 

                {FIXED [num_2] : [type_1]}

                 

                Does this make any sense in the way of getting the number of type_1 values distinctively within a num_2 value? It does, right?

                • 5. Re: Calculating how many times a value repeats and checking previous values
                  Michael Hesser

                  Sounds like you've got a lot going on

                   

                  I was looking at it from this perspective (and I'm making up this case since the details of your own are pretty intense):

                   

                  Let's say I have a list of 50 states (Alabama thru Washington)...

                  And each of those states have a number of Cites...

                   

                  Using an LOD expression, we can relatively easily count:

                  How many Cites per state include the letter "A"

                   

                  Cities_With_A

                  {fixed [City]:max(iif(contains([City],"A"),1,0))}

                   

                  You can of course filter on this value so that only States with 2 or more Cities that include the letter "A" appear.

                   

                  We can also change "A" into a dimension, so we run through the whole alphabet, if you want.

                   

                  I realize this is a long stretch based on what you're asking, but maybe there a kernel in there that will allow you to apply it?

                   

                  All the best-- and you're in good hands: the Forum is filled with clever problem solvers!

                   

                  --Michael

                  • 6. Re: Calculating how many times a value repeats and checking previous values
                     Arthur Valle

                    Hey, Michael Hesser!

                     

                    That would be the actual solution to both needs. Yesterday when i read your last answer I was wondering the Fixed function would just solve the "first need". But now I realize how it can solve both, though. We have a Kernel here and that would be the way to go, at least for now.

                     

                    I had tried using fixed, as I told you before, but I wasn't getting the number I should. It looks like I got things right this time and it's working fine. If I have any other problem I know I can find help over here.

                     

                    Thanks a lot.

                     

                    Arthur.