Two common tools used in Alteryx for data manipulation is the transpose and crosstab tool. It is important to understand the difference between the two tools and is crucial for data analysts to learn because it offers quick ways to reshape and organise data in Alteryx. Depending on what charts you want to produce, data needs to be formatted appropriately. The transpose and crosstab tool in Alteryx offers a quick way to format the data.

 

Transpose Tool

The transpose tool is used to change data from a wide to a long format.

Let’s take a look at an example. We can see that our data is in wide format where the field names represent the types of fruit sold and the first row of data contains the total sales. Using the transpose tool, let’s format our data to change from a wide format to a long format.

Transpose tool Alteryx workflow:

1. Data input tool

We will start the workflow in Alteryx by bringing in the data input tool to connect the data.

2. Transpose tool

Afterward, we will use a transpose tool and configure the tool settings to get the desired output. In this case, the key columns are the field names and the first row contains the sales data. Since we don’t have a column with the field names, we do not need to select anything in the key columns. We will just tick the names of all the products under the data column as this is the data that we want to populate our columns with.

3. Select tool

We can bring in a select tool to rename the fields to make more sense when we export our data in visualisation tools. In this example, we can rename the ‘Name’ column to ‘Product’ and ‘Value’ column to ‘Sales’.

4. Data output tool

Lastly we will use a data output tool to export our data into our desired file type.

 

Crosstab Tool

The crosstab is used to transform data in a long format into a wide format.

Let’s look at another example where we use the crosstab tool to transform long format into a wide format. Using the final output from the transformation example, we will transform it back to its original format.

 

 

 

 

 

Alteryx crosstab workflow:

1. Data input tool

We will use the data input tool to connect our data to the Alteryx workflow.

2. Crosstab tool

We’ll start this off by bringing in the crosstab tool and configuring it like so. We will not be selecting anything under the ‘Group data by these values’. This is because this section tells the tool for the first column what each row of data contains. In our example, we only want one row of data so we will leave this unticked. Under ‘Change Column Headers’, we will choose Product which will populate our field names across the table. Under ‘Values for New Columns’, we will choose Sales to populate our table with the sales data. Under ‘Method for Aggregating Values’, we will select sum. In our case, we only have one value so we will only be finding the sum for one value.

3. Data output tool

As we did with the transpose example, we will use the data output tool again to export our file into a desired file types.

To summarise, the transpose tool transforms data from wide to long format and the crosstab tool transforms data from long to wide format. A tip to remember which tool does what transformation is that the crosstab tool transforms data to go across and the transpose tool does the opposite.

The Data School
Author: The Data School