9 Replies Latest reply on Apr 17, 2012 1:26 PM by Julia Hennelly

    Easy way to map to/from in Tableau?

    Jay Chang

      I have a file with 80K+ employees.  Each employee record has a home ZIP as well as a work location ZIP.  I'm trying to figure out how I can show how many employees come to a given work location ZIP from a given home ZIP.  This is sort of like the flight path sample that is reviewed in the Tableau training (http://www.tableausoftware.com/support/knowledge-base/using-path-shelf-pattern-analysis).  However, to create that map in the training example required every to/from pair to have two rows of hand-created data.  I can't do this for 80K employees.  Is there another way to do this?

        • 1. Re: Easy way to map to/from in Tableau?
          Richard Leeke

          You can use a custom SQL connection to present the data from your input file to Tableau in the structure needed for that representation.

           

          Here's (roughly) what you need to do.  I haven't actually tried it, so I might not have got it quite right.  ;-)

           

          Let's say your raw data has these columns:

           

          [employee_id], [home_zip], [work_zip], [other employee detail fields]

           

          You would need a custom SQL connection like this:

           

          SELECT

          [employee_id] AS [employee_id],

          [home_zip] AS [zip],

          [home_zip] + " to " + [work_zip] AS [path_id],

          1 AS [path_order],

          [other employee detail fields] AS  [other employee detail fields]

          UNION ALL

          SELECT

          [employee_id] AS [employee_id],

          [work_zip] AS [zip],

          [home_zip] + " to " + [work_zip] AS [path_id],

          2 AS [path_order],

          [other employee detail fields] AS  [other employee detail fields]

           

          Then you (should!) just need to drop [zip] and [path_id] on Level of Detail, select a mark type of line and put [path_order] on the Path shelf and SUM([Number of Records]) on the size shelf (say).

          • 2. Re: Easy way to map to/from in Tableau?
            Jay Chang

            OK so this gets me partly there.  Some of the issues I'm running into:

             

            *  I don't have the lat/long coordinates for the ZIP codes, so I'm relying on Tableau to generate those.

            *  I can't seem to get the lines to connect the two ZIPs in the path.

             

            I think the second is related to the first.  Is there any way to get Tableau to generate different lat/longs based on the Home vs. Work ZIP?

            • 3. Re: Easy way to map to/from in Tableau?
              Richard Leeke

              You shouldn't need the lat/long - Tableau geocoding will take care of that (as long as the zip codes are in the country you have set as your default location - othrwise you would need to have a country field on the view, to.

               

              Sometimes I have trouble with the path shelf.  You may not even need it in this case as there are only 2 points for each line segment.  Or you could try putting ATTR([path_order]) on the path shelf (i.e. select Attribute from the pill for path_order).

              • 4. Re: Easy way to map to/from in Tableau?
                Jay Chang

                Sorry, I appreciate your prompt help but it's not working.  Does it matter that the generated Lat/Long are measures rather than dimensions?  I don't see any way to convert generated lat/longs to dimensions but the Tableau example relies on those fields being dimensions.

                • 5. Re: Easy way to map to/from in Tableau?
                  Jay Chang

                  Somehow, I got it working.  I basically started over from scratch by selecting Lat, Long, and ZIP then clicking Show Me.  Tableau then built my map.  I added PathID to my level of detail and voila - the lines were there.  Now I can refine it so that I can choose specific work locations and see which employees travel to that work location.  Thanks for all your help!

                  • 6. Re: Easy way to map to/from in Tableau?
                    Richard Leeke

                    Sample attached.  Post an example if you're still having trouble.

                    • 7. Re: Easy way to map to/from in Tableau?
                      Steve Scivally

                      Looking at that link you provided, is there a way to display/label the path itself? I currently have the geo-points graphed and have even figured out how to draw the vectors between the points but I can only get the label to display on the geo-point... Any ideas?

                      • 8. Re: Easy way to map to/from in Tableau?
                        Martin Luxhøj

                        Richard,

                         

                        Do you have an approach where you have a lat and long coordinate for each zip code? This would help me tremendously!!!

                        • 9. Re: Easy way to map to/from in Tableau?
                          Julia Hennelly

                          I have a similar but need a little more information on the Viz which is not working quite as expected.  I am trying to show where associates currently live (zip code), the office they currently commute to (path should be shown in blue) and the path for their new commute.  I would like to show current offices (there are 8) on the map and actually be able to dynamically enter the zip code for the new site. 

                           

                          Taking it further I need distances for each commute and be able to show percentage of employees where their commute increase is less than a variable percentage.  Ideally rings around the new site with percentage of associates within 5 miles, 10 miles, 20 miles etc.

                           

                          So right now I am at stage 1 and do not seem to be having much luck with that - only way I can think to show current office and new office is to annotate the map???

                           

                          I am a novice user so calculating distances - not sure how to do that.  I think I need latitude and longtitude which I have on another spreadsheet so can bring that data into the data set? 

                           

                          Can anyone point me in the right direction for examples of similar ideas.  I will keep searching the forum which seems to have a ton of information.

                           

                          I am attaching a packaged workbook of where I am at if anyone feels like pointing me in a direction!

                           

                          Thanks,

                          Julia