Aggregate
The Aggregate transform is extremely powerful, due to its multiple applications.
In the broadest possible definition, it allows the user to inject one or more records into each child that contains records.
Example
If there were two child records, the aggregate transform allows the user to create a third record. Multiple records can be added to create a fourth, fifth, sixth, etc.
The records inserted by the aggregate transform are termed 'Calc Records' as shown on the user interface.
The screen-shot below shows new Calc Records added to the original dataset:
Aggregate Applications
Further to injecting records, the Aggregate transform has three other applications:
- Each field within every Calc Record has the facility to specify a formula, therefore separate logic can be specified for each 'Calc Record'.
- If set to 'Accumulate Values', each Calc Record will display all the values from the original container records.
- The original records within the record container can be deleted, to leave only the 'Calc Records', mimicking a traditional aggregate function in SQL.
Uses for Aggregation
The Aggregate transform is full of potential and requires a number of examples to demonstrate its complexity.
- Normalisation/De-normalisation
- Due to the way the Aggregate transform builds or adds records to an existing dataset, it can be used to de-normalise a dataset into something resembling a field-value type structure.
- Nominal Ledger (G/L) Journal Imports
- If importing Journals, the journal needs to be balanced. If the nominal journal being imported includes only one side of the transactions, an aggregate transform can be used to build a record to balance the journal.
- Tax Calculation
- Similarly, an aggregate transform can be used to build a Tax record. Use the aggregate record to generate a new record which sums up the constituent parts of the tax.
- Surcharge or Service Charge on Sales Orders
- Sales orders may contain a surcharge, warranty, delivery charge, or something similar, where the amount of the surcharge is dependent the details. In this case the Calc Record can be used to both insert a new record, and to calculate the appropriate value.
Using Aggregate Functions
The aggregate functions (Sum, Min, Max, Count) provide functionality to query values within a field marked to Allow Value Accumulation. See Aggregate Functions.
Aggregate > Field Mapping
Current Transaction Id
Drop-down contains transactions available for editing.
When transaction Id selected, it is open for editing. In the screen-shot above, 'OrderDetails' is open.
When a new transaction Id is selected, the current selection is automatically saved.
Delete Child Records
When selected, the original container records making up the Calc Record are deleted.
This creates an aggregation similar to a group-by clause of an SQL statement.
When not selected, the container records remain in place in conjunction with the new Calc Record(s).
Calc Records
The drop down from which to select the Calc Record to edit.
Add Button
Create a new Calc Record.
Edit Button
Edits the Calc Record currently selected in the drop down.
Delete Record
Deletes the currently selected Calc Record.
Aggregate > Field Mapping > Add/Edit
Description
The description used to identify the aggregate record.
Field Value/Script Formula
Each Calc Record is given a formula which is validated for each record making up the aggregation. The formula works exactly like a filter operation: if evaluation succeeds, the record is included in the aggregation; if it fails, it will not be included.
By default this is set to True, to include all records.
Aggregate > Field Mapping > Add/Edit > Edit Row
Allow Value Accumulation
When selected each, of the records making up the aggregation are accumulated, so that multiple values are stored in a single field.
When not selected, the first value to make up the aggregate record becomes the value of the field.
Enable Script Evaluation
If selected, the Field Value/Script Formula field is evaluated. If the field can be successfully evaluated the result of the formula is assigned to the field.
If the field cannot be evaluated because of an error, an exception is generated and logged onto the Audit Report. Then, depending on the Transform Error Action instruction, the transform may reject record or abort.
If unchecked, the Field Value/Script Formula field is treated as a literal. Any field references are replaced and the result (i.e. what is written the Field Value/Script formula field) is set to the field.
Field Value/Script Formula
The formula (or literal word) to set to the field. If left blank the expression is not evaluated, and the field will take the first value of the original container records.
Aggregate > Audit
Supported Counters
- PROCESSED
- Incremented for each record being processed within the Aggregate transform.
- INSERTED
- Incremented for each ‘Calc’ or Aggregate record being generated as part of the transform.
- UPDATED
- Incremented for each ‘Calc’ or Aggregate record being generated as part of the transform. The INSERTED and UPDATED counters will typically be equal.
- ERRORS
- Incremented for each non-handled error on a formula.
Action on Transform Error
It is highly recommended this is set to Abort. Setting to Reject Record or Continue will allow IMan to continue processing irrespective of an error AND may cause records to be erroneously added to the dataset.