Pivot Tables - 23.1

Spectrum Dataflow Designer Guide

Version
23.1
Language
English
Product name
Spectrum Technology Platform
Title
Spectrum Dataflow Designer Guide
First publish date
2007
Last updated
2024-05-09
Published on
2024-05-09T23:01:03.226155

A pivot table aggregates and transposes column values in the flow to make it easier to analyze data visually. With pivot, you can arrange input columns into a cross tabulation format (also known as crosstab) that produces rows, columns and summarized values. You can also use fields as input and not display them. You can use pivot to pivot on two dimensions or to group aggregate data on one dimension.

This example shows sales data for shirts.

Table 1. Input Data
Region Gender Style Ship Date Units Price Cost
East Boy Tee 1/31/2010 12 11.04 10.42
East Boy Golf 6/31/2010 12 13.00 10.60
East Boy Fancy 2/25/2010 12 11.96 11.74
East Girl Tee 1/31/2010 10 11.27 10.56
East Girl Golf 6/31/2010 10 12.12 11.95
East Girl Fancy 1/31/2010 10 13.74 13.33
West Boy Tee 1/31/2010 11 11.44 10.94
West Boy Golf 2/25/2010 11 12.63 11.73
West Boy Fancy 2/25/2010 11 12.06 10.51
West Girl Tee 2/25/2010 15 13.42 13.29
West Girl Golf 6/31/2010 15 11.48 10.67
North Boy Tee 2/25/2010 17 16.04 10.42
North Boy Fancy 2/25/2010 12 11.56 12.42
North Girl Tee 2/25/2010 16 12.32 18.42
North Boy Golf 1/31/2010 18 11.78 13.23
North Girl Tee 2/25/2010 12 18.45 11.64
North Girl Golf 2/25/2010 14 11.23 19.85
North Boy Fancy 1/31/2010 16 12.54 13.42
North Girl Tee 2/25/2010 17 181.73 15.83
South Boy Fancy 1/31/2010 19 14.15 13.42
South Girl Tee 2/25/2010 11 11.85 12.92
South Girl Fancy 1/31/2010 13 11.54 14.35
South Boy Tee 2/25/2010 15 14.14 14.73
South Boy Golf 2/25/2010 16 17.83 17.83
South Girl Fancy 6/31/2010 11 18.24 12.35
South Girl Tee 1/31/2010 20 19.94 12.95
South Boy Golf 2/25/2010 12 21.25 19.56

We want to be able to determine how many units we sold in each region for every ship date. To do this, we use pivot to generate this table:

Table 2. Pivot Table
Region 1/31/2020_ShipDate 2/25/2020_ShipDate 6/31/2020_ShipDate
East 32 12 22
North 34 88  
South 52 54 11
West 11 37 15

In this case, the column is Ship Date, the row is Region, and the data we would like to see is Units. The total number of units shipped is displayed here using a sum aggregation.