The Tabular Transformation BizApp provides a Pivot as well as Unpivot Command. To ensure that these are used properly, we will outline 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

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 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:
    - Data Headers,
    - Starting and Ending Pivot Column Names, or
    - Starting and Ending Pivot Column Index.

    You only need to specify one of these three available options.

When using the Data Headers option, each of the fields that need to be unpivoted is specified. The Starting/Ending Column Name/Index parameters should not be used with the Data Headers parameter. In the above example, the values entered would be JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC. Press the <Enter> key after entering each column name.

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. The Data Header and Starting/Ending Column Index parameters should not be used with the Starting/Ending Column Name parameter. In the above example, the Starting Column Name parameter value would be JAN and the Ending Column Parameter Name would be DEC.

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. The Data Header and Starting/Ending Column Name parameters should not be used with the Starting/Ending Column Index parameter. In the above example, the Starting Column Index parameter value would be 5 and the Ending Column Parameter Name would be 16.

📓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 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 unique combination of the 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.

Example 1: 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

Example 2: 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

Example 3: 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

Did this answer your question?