Pivot Table Example - 23.1

The input data that shows shipping information from a fulfillment department:

Region,State,County,ShipDate,Unit
East,MD,Calvert,1/31/2010,
East,MD,Calvert,6/31/2010,212
East,MD,Calvert,1/31/2010,633
East,MD,Calvert,6/31/2010,234
East,MD,Prince Georges,2/25/2010,112
East,MD,Montgomery,1/31/2010,120
East,MD,Baltimore,6/31/2010,210
East,VA,Fairfax,1/31/2010,710
West,CA,SanJose,1/31/2010,191
West,CA,Alameda,2/25/2010,411
West,CA,Los Angeles,2/25/2010,
West,CA,Los Angeles,2/25/2010,215
West,CA,Los Angeles,6/31/2010,615
West,CA,Los Angeles,6/31/2010,727

To determine the number of shipments that went out on each shipping date for each state, create a pivot table by configuring the Group Statistics stage as:

• Operations tab > Input Fields = County, Region, ShipDate, State, Unit
  • Rows = State
  • Columns = ShipDate
  • Operations = Assign Sum of Unit to SumOfUnit
• Fields tab > Stage options = Add the exact dates in the grid that appear in the ShipDate field of the flow input data, and select the Operation values to be displayed for each of the column values.
• Fields tab >

On clicking OK in the Add Field window, the output columns to be created are automatically listed in the Fields tab. These output columns are a Cartesian product of the exact input values and the operations you selected in the Add Field window.

State,1/31/2010_GroupCount,1/31/2010_ComputationalCountSumOfUnit,
1/31/2010_SumOfUnit,2/25/2010_GroupCount,2/25/2010_ComputationalCountSumOfUnit,
2/25/2010_SumOfUnit,6/31/2010_GroupCount,6/31/2010_ComputationalCountSumOfUnit,
6/31/2010_SumOfUnit
VA,1,1,710,,,,,,
CA,1,1,191,3,2,626,2,2,1342
MD,3,2,753,1,1,112,3,3,656