4 Replies Latest reply on Jan 9, 2013 1:36 AM by Egor Larin

    How to compare  one code name to other by count of rows

    Egor Larin

      Hi there,

       

      I've got a problem to create nice filtering dashboard.

       

      I've got CODE A01, A02,...and got IDs (check attach)

      So i want to create compare mechanism like this:

       

      I'd like to compare CODE A01 with other CODEs in order to establish the number of common IDs. i'd also like to change A0.. CODEs so that i could re-calculate using selected/chosen codes.

       

      So this is liitle part of big data. I've got more than 2 000 000 rows, so i need fast method.

       

      Please, help.

       

      Thx for your time.

       

      Egor

        • 1. Re: How to compare  one code name to other by count of rows
          Catherine Rivier

          Hi,

          Maybe I'm reading your issue wrong, but I think this should be a pretty simple fix.

           

          See the attached.  I made ID a Dimension.  Then I created a new calculated field, UniqueCODE, which counts all distinct CODE values per group.  This will show you how many CODEs have the ID in common.

           

          Then a simple filter in Tableau will recalculate every time you change the filter.  So if you simply add CODE to be a quick filter, it will recalculate each time based on your selected values.

           

          Is this what you're looking for?

          • 2. Re: How to compare  one code name to other by count of rows
            Egor Larin

            Hi Catherine

             

            Eem, you didn't catch my idea(

             

            Look, when i choice A01 (it must be a parameter, which make a compare way) Tableau must calculate how many IDs is repeating in each CODE. Like Source and Target - Source A01 have with Target A02 15 same IDs, Source A01 have with Target A03 10 same IDs. It must be list of CODE with  horizontal bar (value of same ID)

             

            I think it must be excluding filter based on parameter.

             

            Have an idea?

            • 3. Re: How to compare  one code name to other by count of rows
              Catherine Rivier

              This is really challenging - I don't know whether it can be done with the data as-is in Tableau (but maybe someone else has an idea?)

               

              I think you'll need to do some restructuring of the underlying data, if at all possible.  You say you have 2 million rows, so I'm guessing it's in a database that can take it?  The way I'd set up the data is in a crosstab, so for example, this IDE:

              CODEIDE
              A01830679300
              A02830679300
              A06830679300
              A10830679300
              A14830679300
              A15830679300
              A17830679300
              A25830679300
              A28830679300
              A31830679300

               

              ...would show up like this:

              CODEIDEA01A02A06A10A14A15A17A25A28A31
              A018306793001111111111
              A028306793001111111111
              A068306793001111111111
              A108306793001111111111
              A148306793001111111111
              A158306793001111111111
              A178306793001111111111
              A258306793001111111111
              A288306793001111111111
              A318306793001111111111

               

              I set this up by creating a copy of the table, joining it to itself, and running a crosstab of the data.  I don't know where your data comes from (this SQL was built in Access), but this is the basic idea:

               

              TRANSFORM Count(Table2.IDE) AS CountOfIDE

              SELECT Table2.CODE, Table1.IDE

              FROM Table1 INNER JOIN Table2 ON Table1.IDE = Table2.IDE

              GROUP BY Table2.CODE, Table1.IDE

              PIVOT Table1.CODE

               

              That would allow you to keep the same number of rows (2 million), and easily do the calculations you want.  You'd create 31 new variables (A01-A31), but with just a simple SUM of each of these you'd be able to get the numbers you want.

               

              As I said, someone may have a solution with the data as is, but I really don't know if it can be done.

              1 of 1 people found this helpful
              • 4. Re: How to compare  one code name to other by count of rows
                Egor Larin

                HI, Cathrine.

                It was very difficult to make this calculation in Tableau.So i decided to make external calculation wich aggregat the the number of matches each pair of A.I've made a php+mysql calculation and got a new simple table with A^2:

                 

                Source     Target     Value

                A01          A02          15

                A01          A03          12

                ...

                And with this data i've made a nice filtering dashboard) And i can draw a netgraph with Gephi)))

                 

                It's awesome))

                 

                Thk u for your time)