Transform functions – Sum aggregation

The DTC aggregation functions

Among the pre-defined functions in the TaskCentre Data Transformation Component (DTC), there are two that are classed as aggregation functions, Node Count and Sum.

The focus of this article is on the Sum function.

The purpose of the aggregation functions

The aggregation functions in the DTC aggregate data values during task run time.

They can, for example, be used to;

  • Count the number of sales order lines within the source XML
  • Aggregate the line item total to produce a summary total value per order

The node count function serves the former function and the sum function the latter.

Focus on the sum aggregation function

You access the Sum function from the Aggregation category in the Functions pane of the DTC.

To use the function, you drag an instance of it into the Transformation Mapping area.

You can then drag connectors into place between the function box and the required elements in the data and tool input schemas.

You can also open the function’s dialog box by double-clicking the function icon. This lets you set or check various options.

pic1

Using the sum function

Let’s take a look at a situation where the Sum function is being applied to a data transformation.

pic2

The example above shows a mapping in the DTC for the Web Service Connector tool for a sales order scenario. This mapping involves several functions.

In the example, there are two simple looping functions, one for the sales order headers and the other for each of the sales order lines within the order headers.

There are also two aggregation functions, one a sum and the other a node count.

pic3

Above: Focusing on the sum function, you can see that it is mapped to two data inputs.

There will always be two inputs. One is used to identify the node within which to sum.

The other input is used to identify what we want summed in the data input schema; this is usually one of its nodes.

pic4

Above: The function will sum within each sales order and provide an output of the total combined order value.

The sum aggregate function takes the order header node salesOrderInfo to sum the appropriate element or node.

Sum aggregation uses the element called price found below the sales order lines node for the itemToSum. This will sum the total order value.

pic5

Above: The output is then mapped to an appropriate element in the tool input. This is TotalCost for the sum aggregation.

(In this example, the other aggregation function, Node Count, counts the order lines in a similar manner, to provide their total number).

The Sum aggregate function: In summary

The aggregation functions in the TaskCentre DTC are used to manipulate data before passing it to the tool input schema.

Two types of aggregation function are provided, node count and sum. The node count function counts instances of an input data element and provides a total count, while the sum function sums the values of input data elements and provides a total value.

To use the sum function in a data transformation, you drag an instance of it from the Functions pane of the DTC into the Transformation Mappings pane. You then drag connections into position between the function and the Data Input and Tool Input schemas of the DTC.

The sum aggregate function maps the appropriate header code in the data input schema to sum the selected elements or nodes within it. The aggregated output, a total summed value, is then mapped to an appropriate element in the tool input schema.

You can open the function’s settings dialog box and specify or check various settings, including input and output parameters, mappings and exception behaviour.

Links to related DTC articles