User Tools

Site Tools


transformations:pivot

PIVOT

Category: Transform / Advanced


Description

Creates a matrix table from a straight table using values in one column as the column labels, and values in a second column as the matrix columns' values. This action is opposite to Unpivot.


Use cases

This action is used to make aggregated, cross-tabular tables from data lists.

This action functions similarly to Excel's PivotTable feature.


Action settings

SettingDescription
Column labelsSelect the column containing the values that will become the column labels of the matrix table (across the top).
DataSelect the column containing the values that will become the data in the body of the matrix table (at the
intersections of the rows and columns). Select an aggregation method to apply to the values. Options: Sum,
Count, Min, Max, Any, and Average.
Preserve formattingSelect this option when a column of date/time values will be used for column headers to retain the date/time format.


Remarks

Note that grouping works implicitly in the Pivot action. Groups are formed by the combination of values in other columns that are not labels or data. Values in a group are aggregated into one row. For instance, in the example below, the "Country" column was used for implicit grouping because the two other columns, "Season" and "Medals", were used for the labels and data, respectively.

If a pivoted dataset has only two columns, one of which is used for data, and the other for labels, the resulting dataset will always have only one row.

To drill down to the cross-tabular results using specific dimensions, remove any columns not required in the matrix table prior to inserting the Pivot action.


Examples

Example: Summarize the data below in a matrix table showing the number of medals won by Country (rows) and Season (columns).

Source table: Winter Olympics medals

Country Season Medals
Canada winter 2010 26
United States winter 2010 37
Norway winter 2010 23
Germany winter 2010 30
Canada winter 2014 25
United States winter 2014 28
Norway winter 2014 26
Germany winter 2014 19


Action parameters:

Column labels using the "Season" column
Data from the "Medals" column
Aggregation method = "Any"


Result table:

Country winter 2010 winter 2014
Canada 26 25
United States 37 28
Norway 23 26
Germany 30 19


Community examples


See also

transformations/pivot.txt · Last modified: 2021/07/19 02:23 by craigt