6 Replies Latest reply on Jan 25, 2013 1:48 AM by George Fellas

    Distance between Customers and Stores

    George Fellas

      Hi All

       

      I am trying to map the distance between stores and customers but I have no fields to join.

      I want the User to be able to select a particular store.

      I then want to view all customers within an 'x' mile radius of that store.

      I also want to see if customers always shop at their nearest store.

       

      I am using a Progress database and the data is in two tables.

      I have read a number of discussions in the forums but they don't appear to be the same as my requirement.

       

      I have attached a workbook (but this was created using CSV files).

       

      I did some experimenting and combined the 2 tables into one table using CSV files and did manage to get further:

      Distance to Store2.JPG

       

      The problem is I can't achieve this using the tables I have in the database.

       

      I would really appreciate some help with this.

       

      Many Thanks

      George

        • 1. Re: Distance between Customers and Stores
          Alex Kerin

          Is it the outcode that connects the customer to the store?

           

          If so, looking at your data, I think we can join the tables instead of blending - then the calcs will be much easier.

          • 2. Re: Distance between Customers and Stores
            George Fellas

            Hi Alex

             

            Thanks for responding so quickly.

            The outcode can not be connected between stores and customers.

            A customer may be very close to a store but have a completely different outcode.

            The problem I have is that there isn't a field to join/blend.

             

            Regards

            George

            • 3. Re: Distance between Customers and Stores
              Alex Kerin

              So you don't know which store the customer visited?

               

              One option is to draw concentric circles at a  distance around your stores (but that doesn't help if they actually went elsewhere)

              • 4. Re: Distance between Customers and Stores
                Robin Kennedy

                George,

                 

                You say you had some success by combining the two tables using csv files but you can't achieve this using the tables in the database... Can you explain what you did with the csvs and why you can't do the same in the DB? Any joining you've done with the csvs should be possible in the db too

                • 5. Re: Distance between Customers and Stores
                  George Fellas

                  Hi Robin/Alex

                   

                  There is one salient fact that I forgot to mention.

                  The customers are loyalty customers and may have only just enrolled as new members of the scheme.

                  They may not have even purchased anything from a store!

                  Therefore all I have is a table of stores and a table of customers, with post codes.

                  I want to see where the customers live in relationship to their nearest store.

                   

                  I created a CSV file that contained both customers and stores and I used a Dimension field called "Type"

                  i.e. "Store" or "Customer".

                   

                  The attached Workbook (22/1/13) shows what I achieved.

                  The map looks great but shows all stores and all customers.

                   

                  But I can't achieve this using my database.

                   

                  Regards

                  George

                  • 6. Re: Distance between Customers and Stores
                    George Fellas

                    Hi Robin/Alex

                     

                    I think that I have now overcome the issue.

                    I have joined the Stores and Customers by post-code but have used:

                    store.post-code <> customer.post-code

                     

                    I am working on the premise that a customer will not live very close to a store and so have

                    the same post code.

                     

                    This means that each store will be joined to every customer - exactly what I need.

                    I then select a store (simple filter on store) and then calculate the distance to the selected store.

                    I can then use a parameter to view all customers within a certain radius.

                     

                    The only thing I can't do at the moment is to plot the position of the selected store.

                     

                    Regards

                    George