Reshaping Data in Alteryx

In honour of passing the Alteryx Core exam, I have decided to write a blog post on the Cross Tab and Transpose tools.

The Cross Tab tool allows you to transform your data by shifting row values to column headers.

Here is an example input:

Say we wanted to see for each presidency who was the president and their vice presidents as shown in the output below:

Firstly, we want to group the data by each presidency, which is indicated by the presidentNo column. This will ensure each row in our new output will be unique to each presidency.

Next, we want to select which field will become our column headers and which field will become our values for these new headers. As we want to see the names of the president and vice presidents we would select Pres_or_VP for our headers and Name for our values. (Ticking Retain Special Characters will keep spaces that would otherwise be turned into underscores)

Lastly, we need to decide what happens if there are multiple vice-presidents in a presidency. This method for aggregating values must be selected. We want to see all vice-presidents so we will go with concatenate.

Transpose works the opposite of Cross Tab, it shifts column headers into row values. Although it may seem like you just need to switch a few settings around the Transpose tool looks a little different with a few things you need to consider.

Let's take the example input below:

As you can see, the months are shown as column headers which makes it difficult if we want to aggregate the data and analyse sales on a monthly level. The form we want is shown below:

The first thing we must decide is which columns we want to keep as the key columns. Note that these columns will be duplicated on the rows created by the transposed data columns. If you do not select a key column the data will still be transposed but without any level of detail present in the unselected column. In this scenario, this would just return all the months and their corresponding sale values without showing the product or category they represent.

Next we want to select the columns that will be transposed which in this case is the month columns. These column names are now under a new field called name while the values under these month columns are in a new field called value. At least one data column must be selected for the transpose tool to run.

And that's how you use the two main tools used to reshape data in Alteryx!