9 Replies Latest reply on Jun 16, 2016 8:34 AM by Johan Lindqvist

    Mapping data on a European municipality or 2-digit postal code level

    Johan Lindqvist

      Hi

      I need to map data on a municipality level for most of Europe. I have data in an access table with columns for country, county, municipality and 2-digit postal code (the two first digits). I can assign the municipality data to a geographic role in Tableau, but when I try to map the data I get only some of the municipalities in Denmark, Sweden and Finland, and none of them for Norway. I have looked at the data and it has the correct names for the missing municipalities. How can I get Tableau to map all the municipalities without somehow creating these in a custom file? Shouldn't this be pretty standard data in Tableau? If not standard, where could I find the files to update Tableau and how would I go about it?

      BR

      Johan Lindqvist

        • 1. Re: Mapping data on a European municipality or 2-digit postal code level
          Steve Burger

          Johan,

           

          Would you be able to post a copy of the Tableau Packaged Workbook, or at least the Access table with the geo-data?  Are the municipalities in question large or small? (For example, over 50k?), How is Norway spelled or abbreviated in the Data Table (could Tableau not be recognizing the country code for Norway as valid?)

           

          Posting the data you are using, or at least screen shots, would be more helpful in diagnosing the issue.


          Thanks,

           

           

          Steve Burger

          Tableau Online Deployment Advisor

          • 2. Re: Mapping data on a European municipality or 2-digit postal code level
            Johan Lindqvist

            Hi Steve and thanks for following up on my question.

             

            I have attached an excel file with the Nordic data and a tableau worksheet

            showing the results when I try to map it.

             

               - The country codes are correctly understood by Tableau.

               - I have changed the meaning of the field municipality in the data to

               have the geographic role of County.

               - I then try to map one of the data values on the map but somehow

               tableau doesn't recognize any Norwegian counties/ municipalities and also

               doesn't recognize quite a few in Sweden, Denmark and Finland. This shows in

               the 830 some unknowns, but I can't go through these one by one?

             

            What to do?

            BR Johan

             

            On Tue, Jun 14, 2016 at 5:27 PM, Steve Burger <tableaucommunity@tableau.com>

            • 3. Re: Mapping data on a European municipality or 2-digit postal code level
              Steve Burger

              Johan,

               

              This is a job for custom geo-coding.

               

              1. I was able to find someone, Peter Juel Jensen, on Tableau Public who already created the custom geo-coded data for the municipalities in the countries you wish to map. Here: | Tableau Public 

               

              2. I downloaded them and reassembled them as an excel workbook. There are a number of ways you could do this, you could paste all of it to one big worksheet.  I used excel Get Data From Text function, so I ended up with a tab for each country (CSV file).

               

              3. Since I had one tab in the workbook for each country, I used Tableau's Union function to assemble them in Tableau as one functional data table.

               

              4. I built the map in Tableau desktop using the Polygon function.

               

              5. I then opened your data file (2015 Nordic Companies by Size) in the same Tableau desktop file.

               

              6. I blended the polygon data table with your data on the Municipality field and colored the municipalities from the number of the records from 2015 Nordic Companies by Size to verify the blend works right.

               

              FWIW, I would recommend pivoting your data file so that you don't have a separate measure for each employee count bin value.

               

              I might have to attach the samples in separate response, the are roughly 45mb each.

              • 4. Re: Mapping data on a European municipality or 2-digit postal code level
                Steve Burger

                Was able to get TDEs to decrease the file size.

                • 5. Re: Mapping data on a European municipality or 2-digit postal code level
                  Johan Lindqvist

                  Thanks Steve!

                  That's beautiful! Could you share the ScandinaviaMaster.xlsx file so that I

                  can recreate this is in the bigger tableau workfile? Would you also have a

                  tip on where I could find the same type of solution for Belgium,

                  Netherlands, Luxemburg, UK and Ireland?

                  BR Johan

                   

                   

                  On Wed, Jun 15, 2016 at 6:15 PM, Steve Burger <tableaucommunity@tableau.com>

                  • 6. Re: Mapping data on a European municipality or 2-digit postal code level
                    Johan Lindqvist

                    Hi again Steve

                    I have run into some trouble:

                     

                       - I refined the view of the map (stepped) and noticed that all the

                       values for the Swedish municipalities have disappeared (null)

                       - When I look at the data I see that you have slimmed down the datafile

                       and removed a lot of the fields. I tried to relink the data file to the

                       Tableau workbook, but then I get the next problem

                       - Is it possible to link the Scandinavian municipality data to the

                       original data fields without running into the next problem: I also seem to

                       run out of memory even when Tableau is the only thing I have open after a

                       reboot (see screen shot below). I have 4Gb memory. When I was mapping the

                       files before I did not have this problem?

                       -

                     

                    BR Johan

                     

                    On Thu, Jun 16, 2016 at 2:03 PM, LINDQVIST, Johan <

                    • 7. Re: Mapping data on a European municipality or 2-digit postal code level
                      Steve Burger

                      Johan,

                       

                      I'll attempt to attach here. it's quite a large excel file (41mb).

                      • 8. Re: Mapping data on a European municipality or 2-digit postal code level
                        Steve Burger

                        - Refined view (stepped) values for Swedish municipalities.

                              I tried to reproduce what you've described and cannot. Can you please provide more detail?

                        - Slimmed down data file -

                             Yes, you should be able to simply replace the data file that I've provided with yours.  I did take the liberty of reshaping your data, as having so many measures for the      different employee bins tends to be problematic from a Tableau analytical approach.  Tableau likes 'tall, skinny data' whenever possible.

                             That said I will attach a new version with the un-pivoted data in another post (cannot attach in a response).

                        -Memory issue - Yes, here is the probable issue, that geo-spatial data could be Generalized to reduce the number of vertices in the polygons and thereby decrease the amount of the memory needed. Unfortunately, I don't have the resources to do so.  You might look into https://gallery.alteryx.com/ for something that might help.

                         

                        For Belgium and other European countries.  Check with the Tableau Public account that I linked to in previous response to see of that user has already created those geospatial data files.  Otherwise, check https://tableaumapping.bi/ I recently found UK county TDE data there.

                         

                        Best of luck!

                        • 9. Re: Mapping data on a European municipality or 2-digit postal code level
                          Johan Lindqvist

                          Hi Steve

                          Really appreciate the time you are putting on this.

                           

                          Regarding the stepped value all I did was right click on the color legend,

                          choose edit colors, and clicked stepped colors, see attached picture.

                           

                           

                          Thanks for the tip on UK counties etc. Would it be possible to share the 41

                          Mb Scandinavian polygon excel file via dropbox or something the so that I

                          see how you built it in order to do the same for the other countries? My

                          email is johan_lindqvist@yahoo.se.

                           

                          Don't know what to do about the memory issue.

                           

                          Thanks for your help!

                          BR Johan

                           

                           

                          On Thu, Jun 16, 2016 at 4:49 PM, Steve Burger <tableaucommunity@tableau.com>