Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
JohnJPS
15 - Aurora

The question has been asked numerous times on the forum: how to shift all rows to columns and all columns to rows:

While the Cross Tab and Transpose tools can be combined in straight-forward fashion to solve these, it seems like something that takes literally just one letter in R...

 

outputData = t(inputData)

 

... should be just as simple here. Thus the “Totally Transpose” macro:

 

TotallyTranspose1.png

 

This will shift rows to columns and vice versa, and will also allow the user to specify if any given input column should transition to the output column names.  Perhaps some examples will explain this better than mere words:

 

Sample Usage

 

Sample data used for testing (very similar to the first question in the above links) is as follows:

 

Test Input Data:

TotallyTranspose2.png

  

Without specifying a column for output column names

When no column name is chosen, all data (including the input column names) appears as row data in the ouput.

 

Totally Transpose setting: not using any input field for the output column names:

TotallyTranspose3.png

 

Output Data – these are the results:

TotallyTranspose4.png

 

With specifying a column for output column names

When a column name is chosen, all data in that field is used as the column headers and the data is pivoted accordingly.

 

Selecting that the input field “Specifier” be used for the output column names:

 

TotallyTranspose5.png

 

Output Data:

TotallyTranspose6.png

 

Behind the Scenes

TotallyTranspose7.png

 

It’s a very straight-forward macro: applying a RecordID to anchor the Transpose and Cross Tab; and if desired, moving a specific column into the output column headers.

 

I am attaching a workflow with sample data and the macro at the bottom of this post. You can also download the Totally Transpose Macro from the Gallery here.

 

Performance


In testing the macro, I generated 100,000 rows of dummy data similar to the above. On an aging laptop (dual core i5, 8GB RAM), this ran in 10.3 seconds, including writing the output to a csv. One caution, however, is that if you wish to browse the output in Alteryx (e.g. using a Browse tool), the displaying (rendering) of 100,000 columns may be prohibitively slow. I force-closed Alteryx rather than wait for the display to finish.

 

Download the macro and let me know what you think.  Happy blending!

Comments
Julien
6 - Meteoroid

Just one word : Awesome !

It's so useful ! Alteryx should manage it natively :)

Thanks a lot for your work !

dataMack
12 - Quasar

Excellent work John! 

Ken_Black
9 - Comet
9 - Comet

John,

 

Thanks so much for this! It was 6 nights ago, on 8/24/16, that I began studying for a top coder challenge. I thought about how a complete matrix transpose could be done in Alteryx, so I started writing one!  My proof is in graphic. I called my version matrix_transpose.  

 

Aces are aces for a reason!

 

Ken

 

 

Matrix_transpose.JPG

alastairramlakan
5 - Atom
Is it possible to modify the macro to include transposing the field headers as well? I have tables with financial data and it has dates in the fields and account codes in the records. I need to transpose the tables to do calculations on the account codes and then transpose back to report with the dates as field headers.
charles_huett
5 - Atom

Genius! Bravo!