Example of filtering and grouping data
This example demonstrates how data can be filtered and grouped during a transformation.
What we want to achieve
In addition it is explained how the filtered records/rows can be written to a separate CSV text file for additional processing by the .NET Script Heater. In this example we use the TextFile Adapter on both the READ (left) side and the WRITE (right) side. For input, a short CSV text file reads as follows.
ProductCode;Group;Description
1;1000;Description of product 1
2;2000;Description of product 2
2;2000;Remark about product 2
3;3000;Description of product 3
You will notice that in this CSV text file there are two data rows with a ProductCode value of 2. These are to be consolidated (grouped) by FlowHeater on the WRITE side into one row. The second description will be written into an extra field called "Supplement" on the WRITE side. As a further task we only want to process product groups of > (greater than) 1000.
For 1)
This shows the ProductCode field is connected to the GroupBy Heater. Warning: for the GroupBy Heater to work properly you should ensure the incoming records/rows on the READ side are already in sorted order. If the records are not sorted by ProductCode, you can perform the additional sort needed using the Sort Heater prior to the transformation described here.
For 2)
Here we can see the IF condition on the far left. A single condition of "if Group <= (less than or equal to) 1000" is specified. You can observe the condition if you double click on this IfThenElse Heater. When the condition is met, the value TRUE is output from the Heater and is first passed on to the .NET Script Heater. This will write any records/rows that are filtered out into a temporary file "%TEMP%FlowHeater.txt" by virtue of a few lines of dynamic C# .NET script. This Heater first inspects its first input parameter, the Boolean output (TRUE/FALSE) from the IfThenElse Heater, and if it is TRUE it copies all its other input parameters into the temporary file. Finally the .NET Script Heater passes on the first input parameter without change to the Filter Heater. This Heater decides whether the current record should be filtered (skipped) according to the Boolean value of TRUE. For any other value than TRUE the record is processed normally and continues to the WRITE side.
For 3)
In this part we use the AutoID Heater with its grouping function enabled, so that each new group begins with a value of 1 and increments for each subsequent member of the same group. This value is passed on to two IfThenElse Heaters. The conditions of both Heaters are quite simple, "if input parameter = 1" and "if input parameter = 2". The first IfThenElse Heater outputs to the "Description" field (condition = 1) while the second Heater outputs to the "Supplement" field (condition = 2).
