5 Replies Latest reply on Mar 3, 2013 12:25 PM by Alex Kerin

    Map showing more results for some areas than we have rows in the table

    Brad Mills

      Only thing I can think of is that we use Oracle and the way we load up Tableau is to load one table, and then join all the other tables to it. So we only see one table in the upper left corner of Tableau.

       

      Could it be that it's counting all the referenced fields across all tables?

       

      What we're seeing is we have a map of our province, sorted by postal code. It gives us a heat/filled map, but then if you hover over any of the areas, it shows you the underlying data and the number of results it's showing for some of the areas is a number bigger than the total number of rows that exist...

        • 1. Re: Map showing more results for some areas than we have rows in the table
          Alex Kerin

          What is your sum([Number of Records]). If it's larger than your first table, you have a cross-product on the join.

          1 of 1 people found this helpful
          • 2. Re: Map showing more results for some areas than we have rows in the table
            Brad Mills

            Hey thanks, so here's where it gets weird for me.

             

            I dragged SUM(Number of Records) to a column on a new sheet, nothing else in my viz, and it says 84,055,578.

             

            The table that my data source is labelled as (Table 1) has 5,302,117 rows.


            Beneath that in Dimensions, I have 6 tables listed:

             

            Table1: 5,302,117

            Table2: 27,399,246

            Table3: 24,966,223

            Table4: 191,379,978

            Table5: 18,144,495

            Table6: 511,094

             

            Not sure how it's getting 84m records...

             

            Also I'm using a Polygon map for this viz, so I can have filled/heat maps based on the first three letters of Canadian postal codes. I have a field with 6 digit postal codes, and a computed field that just does a LEFT([POSTAL_CODE],3).

             

            Maybe it's grabbing polygon data.. But... This is a blank sheet with no map, no polygon data at all in the viz.. Just sum number of records.

             

            On my actual viz when I do a sum number of records, I get a number just over 4 BILLION. That must be polygon points.

             

            When I drag something to the Colour bar to colour by CNT(ID) for example, or anything else really, the numbers are always the same.. And a single 3 digital postal code will have more records than what I'm filtering by... In this instance I'm not trying to CNT by number of records, but rather the number of fields I have in a particular column..

            1 of 1 people found this helpful
            • 3. Re: Map showing more results for some areas than we have rows in the table
              Alex Kerin

              Is there a chance there is a cross product? For example if Table A is:

               

              ID         Zip

              1          01871    

              2          03546

               

              and table B is

               

              ID          Name

              1          St Anne

              2          Georgetown

              1          Hammersmith

               

              and we do a join of any sort on this based on ID, we will get

               

              ID          Zip          Name

              1          01871       St Anne

              1          01871       Hammersmith

              2          03456       Georgetown

               

              If there is ever more than one row of the joined data you will get that increase in rows. If you have multiple joins, then it increase can occur both ways. Then there's also the effect of left vs inner joins on which rows are returned depending on whether the joining field is found in both or just one table. Without knowing more about your data, it's impossible to know where the issue is - you need to do some error checking, just joining a few tables together.

              • 4. Re: Map showing more results for some areas than we have rows in the table
                Brad Mills

                That's exactly what's happening.. Without being an SQL wizard, what's the way to go about this? Do I create the viz again and only include the necessary joins? We have the same ID's across all the tables which is what we join by...

                • 5. Re: Map showing more results for some areas than we have rows in the table
                  Alex Kerin

                  Look in each of the tables for where ID appears more than once. I'm no SQL expert, and this code is for MS I believe, but something like this would work: http://stackoverflow.com/questions/4434118/select-statement-to-find-duplicates-on-certain-fields

                   

                  Equally you could bring in each table individually to Tableau and drag the ID over, and then filter on sum(number of records)>1 to see if there are repeated lines