Transposer converts columns to rows. Transposing data is the opposite of pivoting data using the Group Statistics stage, which transforms row data into columns.
To understand Transposer, consider the following example. A table contains four quarters of sales data and you want to add all the revenue generated and to analyze the growth achieved in first three quarters. To accomplish this, use Transposer to create a column containing all the revenue of three transposed quarters. Using Transposer to add all the revenues generated in different columns into a column potentially improves performance instead of adding them in different columns.
The following table explains the options in the Transposer dialog box.
Option | Description |
---|---|
Transposed fields header | Type a header name for the column that will contain those columns which are to be transposed. This new column is automatically added to the dataflow. |
Transposed values header | Type a header name for the column that will contain the transposed column values. This new column is automatically added to the dataflow. |
Retain transposed fields | Check this option to retain all the transposed fields as columns in the output. |
Field Name | Displays all the column headers of input file. |
Type | Displays the data type of the respective fields (column headers). The columns to be transposed should have compatible data type in the input source file. Below is the compatibility matrix. The tick marked grids correspond to the compatible data types. |
Transposed | Check the box next to each field that you want to convert to a column. In order to prevent a column from getting transposed and retain it in the output, clear the check box. |
Example Use of Transposer
The following input data contains four quarters of sales by store. Note that Q1, Q2, Q3, and Q4 represent four quarters of sales (in millions).
Store (US) | Q1 | Q2 | Q3 | Q4 |
---|---|---|---|---|
New York | 100.00 | 200.10 | 300.00 | 400.00 |
California | 250.10 | 450.00 | 550.00 | 650.00 |
Illinois | 150.00 | 250.10 | 350.00 | 450.00 |
The cases mentioned below illustrate the behavior of Transposer using the options provided in the stage. Note that Quarter is the column name for Transposed fields header and Revenue is the column name for Transposed fields values.
Case 1
Store (US) | Quarter | Revenue | Q4 |
---|---|---|---|
New York | Q1 | 100.00 | 400.00 |
New York | Q2 | 200.10 | 400.00 |
New York | Q3 | 300.00 | 400.00 |
California | Q1 | 250.10 | 650.00 |
California | Q2 | 450.00 | 650.00 |
California | Q3 | 550.00 | 650.00 |
Illinois | Q1 | 150.00 | 450.00 |
Illinois | Q2 | 250.10 | 450.00 |
Illinois | Q3 | 350.00 | 450.00 |
Case 2
Store (US) | Quarter | Revenue | Q1 | Q2 | Q3 | Q4 |
---|---|---|---|---|---|---|
New York | Q1 | 100.00 | 100.00 | 200.10 | 300.00 | 400.00 |
New York | Q2 | 200.10 | 100.00 | 200.10 | 300.00 | 400.00 |
California | Q1 | 250.10 | 250.10 | 450.00 | 550.00 | 650.00 |
California | Q2 | 450.00 | 250.10 | 450.00 | 550.00 | 650.00 |
Illinois | Q1 | 150.00 | 150.00 | 250.10 | 350.00 | 450.00 |
Illinois | Q2 | 250.10 | 150.00 | 250.10 | 350.00 | 450.00 |