GroupBy Heater, grouping several records to one
The GroupBy Heater makes it possible to summarize equivalent data in several records/rows from the READ side to a single record/row on the WRITE side. If the FlowHeater data type BOOL (Boolean TRUE/FALSE or Yes/No value) is assigned and the value is TRUE (Yes) then the record is grouped. If alternative FlowHeater data types are assigned, then the GroupBy Heater decides according to two consecutive equally valued fields.
1; the first line with 1
1; the second line with 1
2; first line with 2
3; first line with 3
In this instance the GroupBy Heater is assigned to the first field and the data for the first two records/rows, having an equal value of 1, would be summarized or grouped into a single record/row on the WRITE side.
This Heater can be assigned several inputs, these are combined together to determine a grouping hierarchy.
Note: The grouping function only works properly when the incoming data is delivered in sorted sequence. If the READ data source does not provide a native sorting feature, as is often the case with text files, then you must first sort the incoming records/rows with the help of the Sort Heater prior to the transformation.
Aggregate functions are consolidation options that are associated with the grouping. Suppose you have a CSV file with an order number repeated on multiple product lines of orders. In the result you do not want individual product order lines, just the total value for each order. In this case you would use the Sum function.
The following aggregation functions/Heaters are available: Count, Sum, Min/Max, Append
Aggregation functions are simply activated using the context menu (right mouse click on the Heater).
- Simple data filter and grouping example
- Expert MS Excel CSV data export
- Grouping data and summing using the aggregation functions