Creating a Pivot Table

A pivot table summarizes data for easier analysis by creating table row and column categories based on input data. For more information, see Pivot Tables.

In the Group Statistics stage options:
  1. In the Operations tab, select a field from Input Fields which contains the data you want to use as the row labels in your pivot table. Then click the >> button next to the Rows field.
  2. Select a field that contains the data you want to use as the columns in your pivot table then click the >> button next to the Columns field.
    Tip: At this point, run inspection to see the results of your selections. This will help you visualize the results of the cross tabulations based on the columns and rows you have selected.
  3. To skip sorting the input records, check Rows and Columns are pre-sorted in the configured order.
    If this field is checked, the stage processes the input records without sorting them.
    Note: Check this if the records are already sorted.
  4. To define the operation to be performed, click the >> button next to the Operations field.
    In the Add Operation window:
    1. Select the Operation to be performed.
    2. In the Input Field section, select the Name and Type of the input field on which the operation must be performed.
    3. In the Output Field section, enter the Name and select the Type of the output field to be generated once the operation is performed.
    4. To fetch the actual count of input records on which the operation is performed as a separate output column, check Get count of records that are computed upon.
      Records with null values are not included in the count ComputationalCount<Operation>Of<InputFieldName>.

      Functions on which Computational Count is supported:
      • Average
      • Variance
      • ZScore
      • Standard Deviation
      • Percentile
      • Percent Rank
      • Sum
      For any other operation, this check box remains disabled.
  5. To define the output fields for each column in the pivot table, click the Fields tab of the stage options.
    Tip: In order to define fields accurately, run an inspection flow once, before this step, to see the column names generated by your data.
    1. Click Add to display the Add Field window.
    2. In the Add Field window, the grid columns are based on the Columns fields you chose in the Operations tab. In these grid columns, enter those values that you see as the column headings on running an inspection flow.
      The records in the column Data can also be populated in one go by using the Import feature. To import data from a CSV or TXT file:
      1. Click Import
      2. Browse the source file using File name field
      3. Enter the Field and Record Separator values
      4. Click OK
      All the records in the file get populated in the Column Data table.
      Note: The source file should not have any header row.
      For example, if you selected an input field called ShipDat e in Columns in the Operations tab, the grid in the Add Field window would have a column labeled ShipDate. In this grid column, enter the exact ShipDate values present in your flow input data, such as 2/25/2010, 1/31/2010.
    3. In the Operation field, select one or more operations for which output columns are generated for each entered column field value. Note that the operation you select only affects the field name and does not control the actual calculation.
      To change the operations listed in the Operation field of the Fields tab, modify the Operations field values in the Operations tab.
      Attention: The Computational Count operation option ComputationalCountOperationOfInputFieldName is listed only if the Get count of records that are computed upon check box is selected while defining the Operation in the Operations tab.
    4. Click Add.
  6. Click OK.

For each input value you entered in the grid above, output columns are automatically created by mapping those against each of the selected Operation values. A Cartesian product of the entered input column values in the grid and the selected Operations is used to automatically generate the final output columns.

The names of these output columns follow the naming convention Data_OperationOfInputFieldName, where Data is the value you specified in the first field, Operation is the operation you selected in the Operation field, and InputFieldName is input column on which the Operation is performed.

Pivot Table Example

The input data which shows shipping information from the 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.

Output

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