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.



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


          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:



              ...would show up like this:



              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)