6 Replies Latest reply on Feb 20, 2013 1:55 PM by Kiran v

    Why does tableau mapping duplicates my lat/long or zip codes?

    Kiran v

      my dataset contains real estate properties spreaded globally but 2 or more properties are within one city but obviously different zip codes. now when i add if these properties are participating in our programs for which i have a colum with ''yes'' or ''no'' and when i drag this column to color it does give me a distinction if it is yes or no but for example we have 4 properties in Calgary CA it will show me only 2. Why? Duplication doesn't really give me any insights. Plus if 2 properties in the same city are participating and non-participating by showing that on one dot doesn't do justice.

       

      Please help.

       

      thank you

        • 1. Re: Why does tableau mapping duplicates my lat/long or zip codes?
          Shawn Wallwork

          Hi Prasad, welcome to the forums! Tableau is a very powerful and complex tool. Which means there are many, many different ways to create maps. So for us to help you track down why you're not getting the result you're looking for, you'll need to post a packaged sample workbook. If you need to, strip out any confidential data. To solve your issue, we'll probably only need the geographic fields and the Yes/No column you mentioned. Don't forget to post a packaged workbook.

           

          --Shawn

          • 2. Re: Why does tableau mapping duplicates my lat/long or zip codes?
            Kiran v

            Hey Shawn,

             

            I have attached a sample sheet for your analysis. It would be great if we could solve this. Its a real nutcracker.

             

            thanks a ton.

            • 3. Re: Why does tableau mapping duplicates my lat/long or zip codes?
              Shawn Wallwork

              but for example we have 4 properties in Calgary CA it will show me only 2. Why?

              Prasad, while you may have 4 properties in Calgary, you only have 2 records for Calgary in your data. Add more records you'll get more marks. Also, in Canada Tableau only recognizes 3-digit Zip Codes. Also, in the US Tableau only recognizes 4 & 5 digit Zip codes, not 9-digit ones. To fix these two issues I wrote this formula:

               

              IF [Country]="Canada" THEN LEFT([Zip Code],3)

              ELSEIF

              [Country]="United States" AND CONTAINS([Zip Code],"-") THEN LEFT([Zip Code],5)

              ELSE [Zip Code]

              END

               

              (See attached.)

               

              While this does make all the Zip codes (for US & Canada) recognizable, and Tableau plots them properly, I suggest you clean up your data in Excel before bringing it into Tableau. The reason I say this is because in the rest of the world you have 363 Unknown Zip codes, so you'd have to write another ELSEIF for every country with a Zip problem. Much better to clean up the data than do these contortions. 

               

              BTW, the reason the Calgary marks are on top of each other is because the first 3-digits for those 2 records are the same. After you post clean data I'll help you work out a way of jiggering them away from each other (or you could search the forums on "jigger".)

               

              --Shawn

              • 4. Re: Why does tableau mapping duplicates my lat/long or zip codes?
                Kiran v

                Shawn,

                 

                This helps a lot, thank you. I understand your point that the data needs to be cleaned prior analyzing on tableau but these zip codes are built in the same manner as you saw in our reporting tool. so seldom can be done with that, unless I clean it up for every country. But do you have the same solution for European codes as you mentioned there are 363 unknown zip codes.

                How would one recognize if a zip for Great Britain or France is right or wrong?

                Lastly do you think having latitude/longitude column would be more accurate than zip?

                 

                thank you

                • 5. Re: Why does tableau mapping duplicates my lat/long or zip codes?
                  Shawn Wallwork

                  Here's what the mapping KB article says:

                   

                  ZIP Code/PostcodeU.S. five digit zip codes.
                  Australian four digit postcodes.
                  Canada Forward Sortation Area; first three characters of the six character Canadian postal code.
                  French five digit postcodes.
                  German five digit postcodes.
                  New Zealand four digit postcodes.
                  U.K. Outcodes; first segment of the five to seven character U.K. Postcode.

                   

                  Here's the full article: http://kb.tableausoftware.com/articles/knowledgebase/mapping-basics

                   

                  It was last updated in Oct. 2012, so that's probably current. If you're in countries not listed, you might have to use the City field instead. But then you're going to need to add a State/Province field, because without it you data produces lots of ambiguous results.

                   

                  The other option you have is to bulk generate your own lat/longs. If you have good (complete) addresses there are some free utilities out there that can generate lat/longs for you. This would help you in another way, because with unique addresses you won't get the marks stacking up on top of each other.

                   

                  Let me know what path you decided to take and I can help get you going.

                   

                  --Shawn

                  • 6. Re: Why does tableau mapping duplicates my lat/long or zip codes?
                    Kiran v

                    I think I am going to go with the lat/long as far as the mapping goes, which will create the spots on the map unique to the codes. if i need help on that I will post another sample set to work.

                     

                    thank you