Alteryx tip: Using Transpose and Cross Tab to swap column values and headers

by George Pattinson

This is a very quick blog post to hopefully save someone some time when trying to move values from a column to column headers.

In this example, a simple set of data has been put together using the text input tool:

Input data table

The aim is to take the values in Field 1 and move them to the column headings whilst maintaining all data. This should be quite an easy task, but has taken me a disproportionately long time to figure out in Alteryx - and the solution lies in using the Transpose and Cross Tab tools.

The end result is shown below:

Desired result, and workflow above

Step 1: Use the Transpose tool to pivot Field2-5 column headings into the table, whilst selecting the  Field1 as the key column. The configuration is as follows:

Transpose tool configuration

Step 2: Using the Cross Tab tool, move Field1 into the column headers, grouping on Name. The configuration for the Cross Tab tool is as follows:

By grouping on Name in this final step, which is a seemingly irrelevant field, the data is not aggregated into the same cell and the desired result is reached.

Avatar

George Pattinson

Fri 01 Oct 2021

Wed 29 Sep 2021

Tue 28 Sep 2021

Mon 27 Sep 2021

© 2022 The Information Lab Ltd. All rights reserved.