Crosstab and Transpose are two powerful tools within Alteryx. They both require some mental rearrangement of data, and this can be somewhat tricky at first. Fortunately, this post seeks to assist with the learning process and make things clearer.
Transpose
Transpose is the first tool that we can use to change the orientation of our tabular data. It allows us to combine multiple columns into two new columns; one column will contain the previous column names and the other will contain the values previously contained in those columns. This process will naturally create more rows.
Our start point is usually what we call ‘wide data’ (one column for each variable) and our result can be called ‘narrow data’ (variables and values stacked together in columns).
Have a look at the diagram below to see this in action:
Now let’s see this working with some actual data:
Before
After
It is often a good idea to draw out what you want your data to look like after transposing first. Once you get into Alteryx, the process is simple. First we need to select our Key column(s) —the column that will be frozen in place. In the case of the above example, the key column would be ‘Person’.
The next box shows us which columns are going to be flipped (Alteryx is helpful in unticking our key column for us).
And it really is as simple as that. The difficulty comes is visualising what outcome you want and selecting the appropriate boxes.
Crosstab
The crosstab tool is the reverse side of the coin and the process is a mirror of transpose. With this we are able to make multiple new columns out of values contained in a single column. This creates a wide dataset out of a narrow one. Here is a diagram of what happens:
With crosstab our example dataset is reverted back to how it was originally:
Before
After
In Alteryx, we first need to select which column(s) to group the data by and this is much like our key column(s) in the transpose tool. Again, for the dataset above, we must tick the ‘Person’ column to get back to what we had before we used transpose on the data.
We then need to choose which column contains the values to make our new column headers. In our case this would be ‘Fruit’. Next we must choose which column contains the values for the new columns. In our case this would be ‘Number’.
We also need to select a method for aggregating values. This gives us some extra control over the values that will make up our new columns but in our case we should select Sum or Average to keep the values as they were.
These tools are invaluable for data manipulation and so learn to use them with precision.