Grouping

The Grouping node performs operations equivalent to a SQL GROUP BY clause. Use this component to group records of selected fields and calculate statistical indicators (sum, mean, minimum, and other) for the remaining fields. The component returns one row per group, and the aggregation functions apply calculations to each group individually, not the entire dataset.

Ports

Input

•  Input data source: Accepts an input dataset.

Output

•  Output dataset: Provides the grouped table.

Configuration wizard

The wizard window contains two panes:

• Available fields: Lists the fields of the input dataset.
• Selected fields: This pane provides two lists: Group and Parameters.
  •  Group: Grouping fields.
  •  Parameters: Fields for which the system will calculate aggregation functions.

Parameter aggregation and supported data types

Aggregation type
Sum		•	•
Count	•	•	•	•	•	•
Minimum	•	•	•	•	•	•
Maximum	•	•	•	•	•	•
Mean		•	•
Median		•	•
Mode	•	•	•	•	•	•
Standard deviation		•	•
Unique count	•	•	•	•	•	•
Null count	•	•	•	•	•	•
First	•	•	•	•	•	•
Last	•	•	•	•	•	•
Only	•	•	•	•	•	•
List	•	•

Drag fields from the input dataset into the Group or Parameters lists. Alternatively, use the buttons:  Move to Group (Alt + G) and  Move to Parameters (Alt + S). Use the  Filter field above the Available fields list to search for a field by name.

Configure the aggregation method for each parameter in a separate window. Double-click a field in the Parameters list or use the context menu to open the selection window. Select the required aggregation methods using the checkboxes; each method writes its result to a separate column.

Below Aggregation options, use the Configure List aggregation option area to set up the following parameters:

• Separator: Set the character that separates values in the list. Select a separator from the list or enter a custom value. Available separators include:
  • Comma (,)
  • Comma with space (,␣)
  • Semicolon (;)
  • Semicolon with space (;␣)
  • Space (␣)
  • Windows line break (CRLF)
  • Linux line break (LF)
  • Tab (⭾)
  • No delimiter
• Text qualifier: Set this to enclose each list value in specific characters. Select a text qualifier from the list or enter a custom value. Available text qualifiers:
  • Quotes (")
  • Single quotes (')
  • No text qualifier
• Unique only: When selected, the result list includes only unique values for the selected field. If a value appears multiple times, the list includes it only once.
• Sort options: Select one of the following sort orders for the result list values:
  • No sorting
  • Ascending
  • Descending

The bottom part of the wizard provides two checkboxes:

• Cache group values: Select to cache the result data if you want other nodes to reuse it later.
• Sort result data: Sort the result table by the grouping fields according to their order in the Group list.

Read on: Date and Time Component