How can I reshape data in Alteryx?

by Daniel Watt

When working with data to create visualisations in tableau it often helps to pivot the data.  This means that we go from having a separate column for each metric to a column for metric name and a column for values.

In this example we want to go from data shaped like this:

cross-tab-data-24oct-2016

to data shaped like this:

normalised-data-24-oct-2016

Pivoting the data makes certain visualisations possible in Tableau that would be very difficult or impossible otherwise.  Repeating formatting across sheets is also much faster if you are just filtering by metric rather than using a different field.

There are several ways we can pivot the data for use in tableau:

The advantage of using Alteryx to transpose the data is that it will work on several types of data source.  Tableau’s in built pivot function does not work for live connections, which makes it redundant for a lot of projects.  You can also rename the transposed fields at the same time.  Below is an Alteryx workflow that transposes the data shown above:

workflow-transpose

The red icon is the transpose tool which can be found under the Transform tab.  In this case I have placed the transpose tool between an input (excel sheet) and output (tableau extract).  Once the transpose tool is added to the workflow, click on it to see the configuration settings in the pane on the left:

transpose-config

You need to select whether fields are to be treated as key fields  or data fields.  The key fields are the fields that you will pivot around, whereas the data fields are the fields which will be separated out into individual rows.  The new fields created are named ‘Name’ and ‘Value’.  The ‘Name’ field contains the data fields (weight, IQ, Salary, Height) and the ‘Value’ field contains the corresponding value.  For each name in the database there are now 4 rows (one for each metric) rather than one.  You will also spot that ‘Dynamic or Unknown Fields’ is ticked under the data fields.  This means that any further columns we add to the data will automatically be pivoted when we re-run the workflow.  This could be really useful if your data source has been updated with new columns.

Fri 06 Jan 2017

Thu 05 Jan 2017

Wed 04 Jan 2017