Being able to transform your data is essential in any data preppers toolbelt but sometimes the transformation process can sometimes be difficult to visualise in advance. Here's how I like to remember how the Cross Tab and Transpose tools in Alteryx work.
Cross Tab (Long to Wide)
The job of the Cross Tab tool is to essentially make long tables into wider tables. By which I mean taking the values from columns and converting them to their own respective headers.
There are four key options when configuring the Cross Tab tool; group data by, changing column headers, values for a new column and Method for aggregation.
Say we have three Columns named A, B and C.
- A may have a date which we want to keep constant so we will assign this as the group by a value where everything with the same date will be grouped together.
- B may be a lot of the same values repeating giving context to column C so we will assign B as the new Column header.
- Column C has all the information relating to the categories Column B is providing so C will be our new values but before we are done we need to decide how the values in Column C are going to be summarized in which the different options of aggregation will vary depending on the data type of Column C.
Below is how we would visualize that.
Transpose (Wide to Long)
The job of the transpose tool is essentially the opposite of the Cross Tab tool making wide tables with lots of headers into longer tables with fewer headers.
There are two key options when configuring the Transpose tool; key columns and data columns.
Let’s convert our previous Cross Tab transform back to its original format.
- A is already in the correct format and we want to keep it how it is so we will assign A as key columns.
- B is currently our column headers but we want to move the headers back into one column field so we will assign all columns in B as our data columns
- C are the values inside the B columns so when we move B the values in C will be arranged next to the corresponding B header
Below is how this would look like.
