Pivot Table

Summarizes tabular data values across two specified fields (dimensions) to create a pivot table containing the summarized (aggregated) data, together with sub-totals for each dimension and the grand total.

The input data can be summarized using a range of aggregation functions, specifically: count, sum, min, max and mean.

To configure the Pivot Table node:

In the GroupForRow property, specify an input field to be used for the first dimension of the summarized data. The unique values contained in this field become the group-by categories that are listed (vertically) in the first column in the output pivot table.

In the GroupForColumn property, specify an input field to be used as the second dimension in the summarized data. The unique values contained in this field become the names of the columns in the pivot table that contain the summarized data.

From the AggregationType property, select the calculation that you want to perform over the specified field.

In the AggregateOverField property, enter the name of the field that contains the data to be aggregated.

Sub-totals are generated for each row in the table and for each column in the table. A grand total is also generated for the values in the table.

The node summarizes the contents of a single field, using the specified aggregation method and grouping dimensions.

Example

Your input data represents a year's sales. The data contains fields with the following attributes:

  • The Quarter in which the order was recognized (for example, Q1 - Q4).
  • The Region in which the sale was made (for example, North, South, East, West).
  • The Value of the individual order.

For example, you have the following input data set:

Quarter
string
Region
string
Value
int
Q1 NORTH 19700
Q2 NORTH 9020
Q3 NORTH 2000
Q4 NORTH 11740
Q1 SOUTH 6650
Q2 SOUTH 1840
Q3 SOUTH 16010
Q4 SOUTH 12400
Q1 EAST 5620
Q2 EAST 12240
Q3 EAST 7710
Q4 EAST 24390
Q1 WEST 11180
Q3 WEST 18870
Q2 WEST 14950
Q4 WEST 17780

 

You want to use the Pivot Table node to generate a pivot table containing the total sales Value by Region and by Quarter. In this case, configure the node properties as follows:

In the GroupForRow property, enter:

Region

In the GroupForColumn property, enter:

Quarter

From the AggregationType property, select Sum.

In the AggregateOverField property, enter:

Amount

 

In addition to generating the quarterly summaries for each region, the pivot table output by the node would also include the following:

  • A row that contains values for the total sales Value for all Regions in a particular Quarter.
  • A column that contains values for the total sales Value for all Quarters, for each Region.
  • The grand total of sales Value for all Regions and all Quarters.
Region Q1 Q2 Q3 Q4 Total
EAST 5620 12240 7710 24390 49960
NORTH 19700 9020 2000 11740 42460
SOUTH 6650 1840 16010 12400 36900
WEST 11180 14950 18870 17780 62780
Total 43150 38050 44590 66310 192100

Properties

GroupForRow

Specify the name of the first field to group the input data by. Unique values from this field are displayed as row labels in the pivot table.

A value is required for this property.

GroupForColumn

Specify the name of the second field to group the input data by. Unique values from this field are displayed as column headings in the pivot table. The field values are displayed as headings, therefore this field cannot have a data type of Unicode.

A value is required for this property.

AggregationType

Optionally specify the type of aggregation to be performed over the specified field. Choose from:

  • Count
  • CountNulls
  • Max
  • Mean
  • Min
  • Sum

The default value is Count.

AggregateOverField

Specify the name of the input field containing the data to be aggregated.

The AggregateOverField property must be specified for all aggregation types except Count. It is not necessary to specify the property when the aggregation type is Count as whole records are counted. For Max, Mean, Min and Sum, the data in the field specified in the AggregateOverField property must be numeric.

NullValueBehavior

Optionally specify how the node should behave when null values are found in the field specified in the AggregateOverField property. Choose from:

  • Error - The node fails with an error of 'Null value found in <field name>' when a record with a null value is found.
  • Log - records with a null value will be removed from the record set and a log message will be written noting this.
  • Ignore - Records with a null value are removed from the record set to be processed.

The default value is Error.

The behavior set by this property does not apply if the AggregationType property is set to Count or CountNull.

Example Data Flow

An "Example Pivot Table Node" LNA is provided with your installation in the following location:

<installation directory>/docs/samples/nodes

If you do not have access to the above location, please contact your administrator.

You can import this example data flow into your installation by using the Import Data Flows/Library Nodes command in the Directory, see the topic Sharing documents for more details.

Inputs and outputs

Input: in1.

Output: out1.

 

 
For support, submit a case online at support.infogix.com | Send feedback on this topic to helpfeedback@infogix.com