8 Replies Latest reply on Dec 9, 2016 8:42 AM by Yuriy Fal

    How to use same field for city or country data?

    jack keenan

      I have a column which contains some geographical data in an inconsistent format- the column may contain city or country data ie

       

      Paris

      France

      Paris

       

      If I assign city as the geographical property I can map the Paris entries but not the France entry and if I assign Country as the geographical property then I can do the opposite. Is there a way to combine the two so that I can map both the city and the country entries to the same map?

        • 1. Re: How to use same field for city or country data?
          Simon Runc

          hi Jack,

           

          I think the answer is possibly (if there was any indicator in your data which flagged which was which, it would be a definitely!).

           

          So what I've done here is duplicate the field...I've then set one to be Geographic type = Country, and the other to be City. I then (using the generated Lon/Lat) map the countries. I then bring the longitude on again, but this time change the detail tile to the (duplicated) country version. I can then dual axis them. So that all seems to work...so why the "possibly"?...Well when working up a solution, it seemed to depend on which countries and cities I used in my sample data. It's not something I've played with too much before, but where Tableau has 2 geographic fields, as it does here (as we've told it the 2 fields are different geographies), it uses one to "help" determine the other. So when it see's London, say, and has a country field...it looks at that country field, but in this case it finds it's London, London and not London, UK...so determines the city field to be "Ambiguous" (it only knows London UK...or if there are other London's, say in the US [there are a lot of shared UK and US city names], the ones in that country)

           

          So the other option we have it to set Country to None...but again this is Ambiguous (I'm guessing where there is the same city name in different countries). So we can set a specific country, but that means if we say UK, it won't be able to plot cities outside the UK (as they are unknown). In this option, you can just choose 1.

           

          In the attached 2 samples, in one, all the cities are in France, and this works fine. However I have another sample where I have London, Nantes, and Lyon...and can only seem to plot the Franch cities, or London (UK cities). As I said I've not played with this too much (apart from the occasional renaming of things), as Tableau does such a good job (generally) of picking up the geographies automatically.

           

          See if it works with you dataset (I'm guessing if you have either all the cities in one country, or the city names are unique on their own, it'll work), and let me know if not...and I'll have a bit more of a play.

          1 of 1 people found this helpful
          • 2. Re: How to use same field for city or country data?
            Norbert Maijoor

            Hi Jack

             

            Find my approach as reference and stored in attached workbook 9.3

             

            1 of 1 people found this helpful
            • 3. Re: How to use same field for city or country data?
              jack keenan

              Hi Simon,

               

              Thank you so much for your helpful reply and my apologies for the delay in responding to you I've just returned to this project. This approach does seem to work to an extent, I can get the city and country data to display together. I still get the same amount of unknown values but I guess that because there are still unknown values in each 'layer' of the map. Thank you again for your advice.

              • 4. Re: How to use same field for city or country data?
                Simon Runc

                I still get the same amount of unknown values but I guess that because there are still unknown values in each 'layer' of the map

                ...spot on. For the city layer all the countries are "unknown", and for the country layer the cities are "unknown"

                 

                Glad it helped

                • 6. Re: How to use same field for city or country data?
                  Yuriy Fal

                  Hi guys,

                   

                  Imho the problem could be defined

                  as a "dirty geo dimension" one.

                  So it needs some cleaning.

                   

                  Please find the attached

                  as an example of such cleaning proc.

                   

                  Hope it could be of some help.

                   

                  Yours,

                  Yuri

                  1 of 1 people found this helpful
                  • 7. Re: How to use same field for city or country data?
                    Simon Runc

                    Thanks Yuri

                     

                    So the Geo tab here... is this just a sample, of what would be a list of all countries and cities (which you'd just get from the web)?

                    • 8. Re: How to use same field for city or country data?
                      Yuriy Fal

                      Thinking about a full Geo Dim.

                       

                      The [city_default] is a fallback,

                      and the filter is to prevent

                      duplicating rows on Left Join.

                      1 of 1 people found this helpful