An example of how to filter and group data, as well as how to consolidate grouped values
This example describes how data can be filtered, grouped and values summed across groups during a transformation. As input let us assume the following small flat file, a report format text file (we do not always have CSV data).
What we want to achieve
You will notice that some lines have unstructured content that we want to ignore. These lines will be filtered out using the Filter Heater.
There are several additional issues to consider. Firstly, the dates are given in a rather unusual format of yyyyMMdd. Secondly, there are blanks in the quantity column that are to be interpreted as the value 1. This value will be defined using the DefaultValue property in the TextFile Adapter. Thirdly, the unit price is expressed in cents. In order to consolidate these as price extended values in dollars across groups we first need to divide these numbers by 100.
For 1: the grouping)
The GroupBy Heater consolidates the data according to the OrderNo field. This Heater detects consecutive equal values for this column in the incoming rows and consolidates (groups) all the data in such rows to a single row. Note: In order for this to work properly, the incoming data must be in sorted sequence. Tip: If the source on the READ side does not supply sorted data, you can first sort on this column using a Sort Heater.
For 2: the filter condition)
Here we initially feed the first field (OrderNo) into the SubString Heater. This Heater extracts just the first character of the string and passes this onto the If-Then-Else Heater. The test condition defined there stipulates that if this first character is an "O" or a "-" then the Filter Heater should ignore the whole line. Note: When the If-Then-Else Heater only passes on one parameter, then the Boolean result of the condition is the result. This value is given to the Filter Heater and so determines whether the entire input row should be filtered out (YES or TRUE).
For 3: the data transformation)
The same input date field is fed into 3 SubString Heaters and these are merged with the addition of 2 static values in X-Value Heaters. From top to bottom, the SubString Heaters extract the month, day and year sections of the string. The String Append Heater recombines these substrings, adding two forward slash symbols defined as static values in two X-Value Heaters, assembling these pieces in a defined sequence into a normal date string. This defined sequence can be seen by a mouse click on the String Append Heater and the sequence can also be amended here.
Tip: This procedure does help to illustrate the flexibility of FlowHeater. However, as it happens the same result could have been achieved much more easily by simply configuring the READ and WRITE Adapters with format settings to convert the date format.
e.g. formats of:
READ = yyyyMMdd
WRITE = MM/dd/yyyy
For 4: the consolidation)
Now we get to the really interesting part of this Definition. The individual product prices must first be divided by 100 so that we convert the cents into dollars with two decimal places. The static value of 100 is derived from an X-Value Heater. After that this value is multiplied by the quantity to give a price extension; if the quantity is blank then FlowHeater will use the default value of 1 as defined in the TextFile Adapter. Finally, the calculated value must be consolidated by summing for each order number group by the grouping function in the Addition Heater.
