community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

How To: Create a Distance Matrix or Mileage Chart

Moderator
Moderator
Created on

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 a combination 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
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

 

 

 

 

 

 

 

 

 

 

 

Moderator
Moderator

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