community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Tool Mastery | Transpose

Aurora
Aurora
Created on
Transpose.png

This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Transpose Tool on our way to mastering the Alteryx Designer:

 

The Transpose tool pivots data for all selected fields. The column headers are listed in the name field and the corresponding data items are listed in the value field. You can also select key fields which will remain unchanged through the transformation. The transpose tool is often used in conjunction with the Cross Tab tool, which essentially works in the opposite direction.

 

Transpose Data to Summarize Rows

I’ll be using some birth rate data from the World Bank Group. I’ve included the relevant data in the attached v11.3 Alteryx workflow Transpose.yxzp. Here is a quick look at the original data in Alteryx:

 

2019-06-18_15-45-04.png

 

Suppose that we wanted to get some basic statistics by country like average, min, max and standard deviation. Since this data goes all the way back to 1960, we’ll let the user select what years they want to include. The Summarize tool is the one for the job, but it needs the data to be oriented differently: it needs the years running down a single column with data points in another column. That is where the Transpose tool can help:

 

2.png

 

For the Transpose tool, we’ve selected a few key fields and then checked all the years as data fields:

 

2019-06-18_15-40-08.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This transforms our original data into this:

 

2019-06-18_15-43-36.png

 

Notice that our 4 key fields remain unchanged and our data fields are transposed.  We use the Filter tool to grab the user-selected years and eliminate null values. Now the Summarize tool can use the value field to calculate the numbers we’re looking for:

 

2019-06-18_15-44-14.png

 

Excel Transpose

For anyone familiar with the Transpose option when pasting in Excel, it’s helpful to note that Alteryx’s Transpose tool is not the same; however, it is possible to transpose data just like excel by adding a few more tools:

 

6.png

 

First we add a RecordID tool before transposing the data using the recordID as the key and all the original fields as data. We then want to add another recordID tool grouped by our first recordID after the transpose, so we use the Tile tool. We then use a Select tool to make sure our Tile_Num field is before the Name field. The Tile and Select tools are used to help the Cross Tab keep things in order. It’s important that the field for sorting (Tile_Num in our case) be before any fields we’re using for grouping in the Cross Tab. Note how the Cross Tab is configured:

 

2019-06-18_15-40-46.png

 

And voila! The result now matches the transpose option when pasting in excel. Feel free to check out the excel file where I’ve transposed the data using excel so you can verify that it matches what Alteryx is doing.

 

Perform Calculations on Columns

About once a month somebody will post on the Community with the same basic concept: they have their data in excel and can write a simple formula that references columns (D1 = A1 + B1). They can copy that formula over and get the solutions for all their columns (E1 = B1 + C1 etc.). How can they do this in Alteryx? We don’t want to write out a separate formula for each column. Someone will quickly come to the rescue and point out that transposing the data will allow them to solve their problem.

 

For our birth rate data, let’s suppose we wanted to calculate the yearly change for each country for all the years. For 1961, we would want Birth Rate1961/Birth Rate1960 -1. For 1962, it would be Birth Rate1962/Birth Rate1961 -1, etc. We can start with the transpose just like in our excel transpose example, and then use the Multi-Row Formula tool to do our calculations before cross tabbing the data back:

 

8.png

 

It’s hard to visualize how this is working, so definitely walk through the data at each step to see for yourself!

 

Setup Name Value pairs for Interface Inputs

You may have noticed in our first example that the List Box tool was referencing an input file that held all of our years to choose from. In order to produce that list from the data, we can use a Transpose and Summarize tool:

 

9.png

 

For the List Box tool, you literally must have a field called “Name” and a field called “Value”. In our case, we want them both to be the years which are in our Name field from the Transpose tool. We’ve setup our Summarize tool to create these fields like so:

 

2019-06-18_15-46-27.png

 

There you have it! The Transpose tool is an extremely versatile tool, but it does take some time to get used to. Just remember that it will always output your key fields plus a name and value field. Notice that it is often used in conjunction with the Cross Tab tool.

 

By now, you should have expert-level proficiency with the Transpose Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

 

Stay tuned with our latest posts every Tool Tuesday by following Alteryx on Twitter! If you want to master all the Designer tools, consider subscribing for email notifications.

Attachments
Comments

Very helpful tool and appreciate the simple description.   

This only took me 5 minutes to read and apply to my data.

Thanks!

Atom

Curious if there is a way to transpose with multiple header rows on the data fields? For instance if row one was year and row 2 was month, and I want to keep both in two different fields.

Alteryx Certified Partner

@Alex_F

There is...but unfortunately can upload an example to an article. Here's a pic of an example...

TwoRowHeader.png

First your input should not use the headers at all...check "First Row Contains Data". This way you get common header identifiers (e.g., F1, F2, F3, etc.)

Then you need to basically separate out the "header" rows from the "data rows. Then you transpose the header rows one at a time, and them join them on the "Name" (which are the F1, F2, F3 designations.

Once you have that, you just join back in the data to the same "Names" and eliminate the RecordID and other unnecessary data.

 

Atom

How does the Select year List Box question work without being connected to anything?