Transposer

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

Suppose you want columns Q1, Q2, and Q3 to be transposed and Q4 to be retained in the output. To do this, check the box under the Transposed header next to each column which is to be transposed. You will now see Q1, Q2, and Q3 as rows whereas Q4 will be retained as a column in the output.
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

Suppose you want columns Q1 and Q2 to be transposed and Q3 and Q4 to be retained in the output. In addition, you also want to retain all the transposed fields (Q1 and Q2) as columns in the output. To do this, check the option Retain transposed fields and the box under the Transposed header next to each column to be transposed. You will now see Q1 and Q2 as rows whereas Q3 and Q4 will be retained as columns in the output along with Q1 and Q2.
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