5 Replies Latest reply on Aug 2, 2013 11:01 PM by George Fellas

    Map 2 different geo locations

    George Fellas

      I am trying to map customers to their nearest store. I have calculated the distances and the viz works perfectly and shows all the customers but I can't show the store on the map. I am using Tableau Version 8.

       

      This what I am trying to achieve:

      Stores and Customers.JPG.jpg 

      I am using these tables:

      Stores and Customers tables.JPG.jpg

       

      They are joined by this statement:

       

       

      Stores and Customers join.JPG.jpg

       

      I have attached a sample workbook.

      I would really appreciate some help with this - I feel it should be so simple but I completely lost.

        • 1. Re: Map 2 different geo locations
          Tom Dewar

          I've been thinking about something similar.  I reckon you need a data set which contains both customers and stores in one table, with a field to categorise them as one or t'other, and another (calculated) field which marks them as selected or no.  The latter should select all relevant customers plus only one store (the nearest).  Map only the selected, and colour/size by category.

           

          Typing this from my iPad, so can't tinker with your example twbx file, but think this should work.  Let us know how you get on.

           

          Tom

          • 2. Re: Map 2 different geo locations
            Shawn Wallwork

            Something like this:

             

            Stores On Map.png

            To be able to get at your data I had to copy it out to Excel, then use this Custom SQL to connect to it:

             

            SELECT [Sheet1$].[customer_latitude] AS [Latitude],

              [Sheet1$].[customer_longitude] AS [Longitude],

              [Sheet1$].[Distance] AS [Distance],

              [Sheet1$].[email-address] AS [email-address],

              [Sheet1$].[location-name] AS [location-name],

              [Sheet1$].[Within Distance?] AS [Within Distance?],

              "Customers" AS [Type]

            FROM [Sheet1$]

            UNION ALL

            SELECT [Sheet1$].[latitude] AS [Latitude],

              [Sheet1$].[longitude] AS [Longitude],

              [Sheet1$].[Distance] AS [Distance],

              [Sheet1$].[email-address] AS [email-address],

              [Sheet1$].[location-name] AS [location-name],

              [Sheet1$].[Within Distance?] AS [Within Distance?],

              "Store" AS [Type]

            FROM [Sheet1$]

             

            This essentially combines the two different Lat/Long fields together and then uses the new Type field to ID them. After you get them combined it's easy to build the viz. I don't imagine it will be to difficult to adapt this strategy to connect directly with your server.

             

            Hope this helps,

             

            --Shawn

            • 3. Re: Map 2 different geo locations
              George Fellas

              Many Thanks Tom and Shawn for your suggestions.

              I've been away for a few days, hence I didn't pick up your responses.

              I had this working using an Excel spreadsheet and combing all the data in one sheet e.g.

              Type            Name               Long     Lat

              Customer     Smith               x          x

              Customer     Adams             x          x

              Customer     Ronaldo           x          x

              Store           London            x          x

              Store          Cambridge        x          x

              Store          New York          x          x

              Although this worked, I still couldn't display the Store Name.

               

              But I can't seem get it working with an enterprise database using 2 different tables.

              I am not familiar with Custom SQL so I am not sure that is an option.

               

              George

              • 4. Re: Map 2 different geo locations
                Tom Dewar

                Hi George,

                 

                If I were you I'd find someone to create you a custom view (query) on your enterprise warehouse that does the hard work for you, then plug Tableau into that.  Give them your spreadsheet as a template and say "I need it to output like this".  Best to do as much of the heavy-lifting of data prep outside of Tableau imo.

                 

                To get the store labelled, have you tried:

                1. creating a calculated field (call it StoreLabel) which equals the store name if the record is for a store, else null (if it is a customer).
                2. Then set Tableau to use the StoreLabel field to label all points.

                 

                Thanks

                Tom

                • 5. Re: Map 2 different geo locations
                  George Fellas

                  Hi Tom

                   

                  I think you are right - I will have to get the data pre-formatted.

                  With Tableau I get the impression that you can spend days and days trying to overcome a problem because of the data e.g. joins, blending, SQL.

                   

                  Thanks for you advice

                  George