Hi Everyone,

Today, I will talk about “Cross tab” and “Dynamic rename” tools in Alteryx, and how these tools might be useful.

Orange = Cross tab

Grey = Dynamic rename

Why Cross tab and Dynamic rename?

Well, the first concept to talk about these 2 tools is Tableau loves tall(long) data sets more than wide data sets. In other words, humans prefer to read and see data sets in a wide format than a tall format.

Quick test:

Which of the data sets below are easier to read?

Wide:

x

Tall:

As a crucial step in data preparation, it’s important to transform data into a format that is optimized for reading in Tableau. This is where the cross-tab and dynamic rename tools prove to be especially effective.

What are the steps?

To get started, you’ll want to connect the cross tab tool to your dataset and configure it accordingly.

Starting from left to right

Key Columns => When choosing a Key Column in Alteryx, you’re essentially telling the software that this specific item should remain unchanged and kept in its original form.

Data Columns => By selecting items in this section, such as months in this case, they will be transformed into columns.

13 of 13 fields => quantity of fields on this view are Country, Jan, Feb, finishing in Dec. so, 12 months + ‘Country’ = 13 fields.

10 records => Quantity of countries.

the output after running the workflow will be:

The quantity of field changed from 13 to 3 and the number of records changed from 10 to 120.

The final step in my data preparation process involves renaming the ‘Name’ and ‘Value’ columns to ‘Month’ and ‘Amount’, respectively. This is where the Dynamic Rename tool comes into play.

To accomplish this, we’ll use the rename mode formula and select both fields. Then, in the bottom section, we can write the expression that will achieve our desired outcome.

The final output:

Now, we can export the file to use in Tableau to create our visualization as below:

Thank you so much for reading.

The Data School
Author: The Data School