|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

Using the Pivot and Unpivot commands with Tabular Transformation

Using the Pivot and Unpivot commands with Tabular Transformation

  • Last updated on

The Tabular Transformation connector provides both a pivot and unpivot command. To ensure that these are used properly, we've outlined the use cases that each command is intended to address.

Unpivot

The unpivot command is for datasets where multiple data columns exist for a single row of data. This layout is common for financial data that includes multiple time periods as columns of data. Below is an example dataset to which the unpivot command can be applied.

Data before unpivot command:

YEAR,MEASURE,PRODUCT,MARKET,SCENARIO,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
FY20,Sales,100-10,New York,Actual,100,200,300,400,500,600,700,800,900,1000,1100,1200 
FY20,Sales,100-10,Massachusetts,Actual,125,225,325,425,525,625,725,825,925,1025,1125,1225

Data after unpivot command:

YEAR,MEASURE,PRODUCT,MARKET,SCENARIO,Period,Amount 
FY20,Sales,100-10,Massachusetts,Actual,JAN,125 
FY20,Sales,100-10,Massachusetts,Actual,FEB,225 
FY20,Sales,100-10,Massachusetts,Actual,MAR,325 
FY20,Sales,100-10,Massachusetts,Actual,APR,425 
FY20,Sales,100-10,Massachusetts,Actual,MAY,525 
FY20,Sales,100-10,Massachusetts,Actual,JUN,625 
FY20,Sales,100-10,Massachusetts,Actual,JUL,725 
FY20,Sales,100-10,Massachusetts,Actual,AUG,825 
FY20,Sales,100-10,Massachusetts,Actual,SEP,925 
FY20,Sales,100-10,Massachusetts,Actual,OCT,1025 
FY20,Sales,100-10,Massachusetts,Actual,NOV,1125 
FY20,Sales,100-10,Massachusetts,Actual,DEC,1225 
FY20,Sales,100-10,New York,Actual,JAN,100 
FY20,Sales,100-10,New York,Actual,FEB,200 
FY20,Sales,100-10,New York,Actual,MAR,300 
FY20,Sales,100-10,New York,Actual,APR,400 
FY20,Sales,100-10,New York,Actual,MAY,500 
FY20,Sales,100-10,New York,Actual,JUN,600 
FY20,Sales,100-10,New York,Actual,JUL,700 
FY20,Sales,100-10,New York,Actual,AUG,800 
FY20,Sales,100-10,New York,Actual,SEP,900 
FY20,Sales,100-10,New York,Actual,OCT,1000 
FY20,Sales,100-10,New York,Actual,NOV,1100 
FY20,Sales,100-10,New York,Actual,DEC,1200

 

Command parameters

The unpivot command has several parameters:

Delimiter The character used to separate the fields in the data file
Aggregation Operator SUM or NONE. Use SUM to aggregate records to a single aggregated data point for all records where values are the same across all columns; choosing NONE will create duplicate rows with the unique data value from each record. SUM is recommended in most cases.
New Column Header The header for the new column to be created. The rows will be populated with the values from the columns to be unpivoted. In the above example, this is the Period field.
Data Column Header: The header for the column for the rows that will be populated with the values being unpivoted. In the above example, this is the Amount field.
Columns to Unpivot

This section can be controlled by any of the following parameters. You only need to specify one of these options:

  • Data Headers
  • Starting and Ending Pivot Column Names
  • Starting and Ending Pivot Column Index

Tips for using Unpivot

  • The Starting and Ending parameters should not be used with the Data Headers parameter. 
  • When using the Starting and Ending Pivot Column Names, you can enter the name of the first column and the last column. Using this set of parameters means that all columns between the Starting and Ending Column specified will be unpivoted.
  • When using the Starting and Ending Pivot Column Index parameters, you can enter the column number of the first column and the last column. The column count is zero-based which means the first column in the file is column zero (0). Like the Starting/Ending Column Name parameters, using this set of parameters means that all columns between the Starting and Ending Column specified will be unpivoted.
  • When using either of the Starting Pivot Column parameters, leaving the reciprocal Ending Pivot Column parameter field blank will instruct the Command to take all columns to the right of the Starting Pivot Column specified. This is particularly useful for data sets that are produced by rolling forecasts.

Pivot

The Pivot command is intended for datasets where a single data column exists and the field values in one or more columns need to be represented as columns of data. Below is an example dataset to which the unpivot command can be applied.

Data before pivot command:

YEAR,MEASURE,PRODUCT,MARKET,SCENARIO,Period,Amount 
FY20,Sales,100-10,Massachusetts,Actual,JAN,125 
FY20,Sales,100-10,Massachusetts,Actual,FEB,225 
FY20,Sales,100-10,Massachusetts,Actual,MAR,325 
FY20,Sales,100-10,Massachusetts,Actual,APR,425 
FY20,Sales,100-10,Massachusetts,Actual,MAY,525 
FY20,Sales,100-10,Massachusetts,Actual,JUN,625 
FY20,Sales,100-10,Massachusetts,Actual,JUL,725 
FY20,Sales,100-10,Massachusetts,Actual,AUG,825 
FY20,Sales,100-10,Massachusetts,Actual,SEP,925 
FY20,Sales,100-10,Massachusetts,Actual,OCT,1025 
FY20,Sales,100-10,Massachusetts,Actual,NOV,1125 
FY20,Sales,100-10,Massachusetts,Actual,DEC,1225
FY20,COGS,100-10,Massachusetts,Actual,JAN,100
FY20,COGS,100-10,Massachusetts,Actual,FEB,200 
FY20,COGS,100-10,Massachusetts,Actual,MAR,300 
FY20,COGS,100-10,Massachusetts,Actual,APR,400 
FY20,COGS,100-10,Massachusetts,Actual,MAY,500 
FY20,COGS,100-10,Massachusetts,Actual,JUN,600 
FY20,COGS,100-10,Massachusetts,Actual,JUL,700 
FY20,COGS,100-10,Massachusetts,Actual,AUG,800 
FY20,COGS,100-10,Massachusetts,Actual,SEP,900 
FY20,COGS,100-10,Massachusetts,Actual,OCT,1000 
FY20,COGS,100-10,Massachusetts,Actual,NOV,1100 
FY20,COGS,100-10,Massachusetts,Actual,DEC,1200

The pivot command has several parameters:

Delimiter The character used to separate the fields in the data file.
Aggregation Operator SUM or NONE. Use SUM to collapse a record with equivalent row values to a single data record. Choosing NONE will create multiple rows for a single set of equivalent row values. Each row will be populated in the column for the row pivoted but null in the other. SUM is recommended.
Pivot Columns

The column(s) for which the row values will be pivoted to a column as a header. A new column will be created for each unique value in the rows for the column selected. At least one column must be specified, but if additional columns are specified then the combination of values from each column specified will be created as a new column.

In the above data sample, if selecting the Measure column, then new columns called Sales and COGS would be created. If choosing Measure and Period, then 24 new columns would be created: Sales-Jan, Sales-Feb, Sales-Mar, and so on through COGS-DEC.
Pivot Delimiter The delimiter that should be used when creating the heading of new columns created by the Pivot. This parameter is only applicable if more than one pivot column is specified.
Data to Aggregate This is the data column in the data set to be pivoted. In the above example, this is the Amount field.
Rows The list of the columns in the original data set that should be preserved as unique values when pivoting the data. Each unique combination of the values in the columns entered in this parameter will be represented as a row in the pivoted result.

 

Pivoting the Measure Column

The Amount field was specified for the Data to Aggregate parameter, and the Year, Product, Market, Scenario, and Period columns were specified for the Rows parameter.

YEAR,PRODUCT,MARKET,SCENARIO,Period,Sales,COGS 
FY20,100-10,Massachusetts,Actual,APR,425,400 
FY20,100-10,Massachusetts,Actual,AUG,825,800 
FY20,100-10,Massachusetts,Actual,DEC,1225,1200 
FY20,100-10,Massachusetts,Actual,FEB,225,200 
FY20,100-10,Massachusetts,Actual,JAN,125,100 
FY20,100-10,Massachusetts,Actual,JUL,725,700 
FY20,100-10,Massachusetts,Actual,JUN,625,600 
FY20,100-10,Massachusetts,Actual,MAR,325,300 
FY20,100-10,Massachusetts,Actual,MAY,525,500 
FY20,100-10,Massachusetts,Actual,NOV,1125,1100 
FY20,100-10,Massachusetts,Actual,OCT,1025,1000 
FY20,100-10,Massachusetts,Actual,SEP,925,900

 

Pivoting the Measure Column, excluding Period from the Rows

The Amount field was specified for the Data to Aggregate parameter, and the Year, Product, Market, and Scenario were specified for the Rows parameter. Because the Period column was excluded, the result aggregated the data from for all time periods for each unique combination of the remaining columns.

YEAR,PRODUCT,SCENARIO,MARKET,Sales,COGS 
FY20,100-10,Actual,Massachusetts,8100,7800

 

Pivoting the Measure and Period Columns

The Amount field was specified for the Data to Aggregate parameter and the Year, Product, Market, and Scenario columns were specified for the Rows parameter.

YEAR,PRODUCT,SCENARIO,MARKET,Sales-JAN,Sales-FEB,Sales-MAR,Sales-APR,Sales-MAY,Sales-JUN,Sales-JUL,Sales-AUG,Sales-SEP,Sales-OCT,Sales-NOV,Sales-DEC,COGS-JAN,COGS-FEB,COGS-MAR,COGS-APR,COGS-MAY,COGS-JUN,COGS-JUL,COGS-AUG,COGS-SEP,COGS-OCT,COGS-NOV,COGS-DEC 
FY20,100-10,Actual,Massachusetts,125,225,325,425,525,625,725,825,925,1025,1125,1225,100,200,300,400,500,600,700,800,900,1000,1100,1200

 

The Tabular Transformation connector provides both a pivot and unpivot command. To ensure that these are used properl...