Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Create a Distance Matrix or Mileage Chart

DanC
Moderator
Moderator
Created

In a recent article (Create an Indexed Map), I mentioned the indexed maps found in the Rand McNally Road Atlas. Well, also found in the Road Atlas are mileage charts, or, distance matrices. These matrices can be easily created in Alteryx. The example below will provide the distance between every store in a dataset to every store in that same dataset. Here's how we did it.

Distance Matrix/Mileage Chart Example

Workflow.png

Procedure

  1. Create a Cartesian Join of your data

    • Using the Append Tool, create a Cartesian Join of all of the records in the dataset.
    • This will give you acombination of every record to every record in your dataset.
    • Don't forget to "Allow All Appends" (learn more about creating Cartesian Joins here).

  2. Measure the distances between all record combinations.

    • Using the Distance Tool, measure the distances between the point combinations.

  3. Flip the data into columns using the Cross Tab Tool.

  4. Use a Select Tool to change the Store Number column to a string.

    • Doing this will prevent the Table Tool from adding commas to this field.

  5. Use a Table Tool to create a formatted table.

    • Add a Column Rule to the Store Number field to format the column as bold.
    • Create a Row Rule in order to force a one decimal place to the distance data.

Here's the workflow which you can also find attached:

Workflow.png

That's it. Feel free to leave any comments or ask any questions.

Attachments
Comments
plim
6 - Meteoroid

Thanks for this information. The key components here were the append and cross tab tool. Using the example I was able to re-create a demo from some point data used to determine distances from the office to the airport!

 

 

  • Append:

 

Append.png

 

 

  • Cross tab:

 

Mileage Chart.png

 

 

 

 

 

 

 

 

 

 

 

DanC
Moderator
Moderator

Great, @plim! Glad you were able to make some use of the example. Thanks for your response!