These two tools are useful for managing and organizing your data within Alteryx, particularly if you want to get your data ready for Tableau. However, it is difficult to visualize what they are doing and what the difference is between the two exactly is. Hopefully, this blog shall make their purpose and differences clearer.
Transpose
This is, in my opinion, the easy of the two tool to understand and use. Transpose can pivot multiple columns into two whilst keeping the previous column names and values and creating more rows. For example, in the below table there is a Item column and a column for each month recording the number of sales.
This layout may be okay for when looking at the table by itself but is not particularly helpful if we wish to make chart with it in tableau which is where the transpose tool comes in. Transpose will allow you to select a key column that will not rotate whilst rotating the others not selected into two new columns whilst keeping all their previous values. In the example above the Item Column would need to remain fixed whilst the month columns rotated. So first when the Transpose tool is open select Item as a Key Column, notice it automatically deselects it from the Data Columns.
After this has been selected run the workflow and the month columns should now be pivoted along with the sales data into two new columns like so with the corresponding Items being automatically input for new rows created.
*Table goes on for longer but cut off due do space
Now it is ready to be input into Tableau to create charts.
Cross Tab
Cross Tab does the reverse of Transpose but it is a little trickier to visualize and does require a little more setting up compared with Transpose. It enables us to take a singular column and make multiple new ones. So let’s say after making the previous table with the transpose tool we now want it back to the previous format.
Input the cross tab tool into the workflow, connect it to the previous transpose tool and open it up. Like when we transposed the table we want the item column to remain in its original position so we select Item in the Group data by the Values box. Next we need to Select Month in Change Column Headers. This will name all the new column headers with each Month name. If using another data set it will rename each new column header with a new unique value within the column selected. Now we wish to input values within the new columns created which in this case will be the Sale Numbers for each item. Select this under Values for New Columns. It is important to note that any column now select either in the Group data by the Values , Change Column Headers or Values for New Columns will not be in the new table. In this example we do not have any more values but this will not always be the case. If they are multiple values you wish to keep in place then select this in the Group data by the Values box. You will need to select a Method for Aggregated in this case we have selected Sum.
After these values have all been selected run the workflow and the table should now look like it did originally.
Well done you made it through. Hopefully this blog was able to make Tranpose and Cross Tab a little easier to understand.