Transform functions – Interleaved merge looping

The DTC looping functions

Among the pre-defined functions in the TaskCentre Data Transformation Component (DTC), there is a group that is used to apply looping in a data transformation.

There are three looping functions:

  • Simple
  • Split-by-type
  • Interleaved merge

We will briefly look at looping in general before concentrating in this lesson on the interleaved merge function.

The purpose of the looping functions

A loop is used to iterate through all the specified elements in a set or array. The iteration is repeated until all the conditions specified for the looping have been met.

The looping functions are used in TaskCentre task design to carry out various kinds of looping during the transformation of data between the data input and the tool input schema.

Looping can affect the flow of data or even, in the case of split-by-type and interleaved merge, change the structure of the data.

Focus on the interleaved merge looping function

You access the Interleaved merge function from the Looping 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 icon 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

What is interleaved merge looping?

pic2

Interleaved merge looping takes data from two or more source nodes and merges it to create one occurrence in the mapped destination node.

Data from each source node is merged sequentially, so that the first occurrence of data in the first node is combined with the first occurrence in the second node, the second with the second, continuing until all occurrences of data have been merged.

If, for instance, there are two source nodes, the first with three records and the second with two, the destination node will contain three records (one with null results in some elements).

Using the interleaved merge function

Some systems do not permit the creation of, for example, order headers and lines in a single operation. This has to be done through separate operations.

This is the sort of situation where interleaved merge can be used to transform data.

Let’s take a look at an example of interleaved merge looping.

pic3

Above: This example shows a mapping in the DTC, where the interleaved merge looping function is being applied to a data transformation.

pic4

You can see (left and above) that the interleaved merge function in this example has two inputs and one output. It is possible for an interleaved merge to have more than two input nodes.

You would only use an interleaved merge where there are two or more inputs that need to be consolidated into one destination node or operation.

pic5

pic6

Above: The two inputs come from the Order_Lines node and the Purchase_Order_Line node in the data input schema.

The Create operation, which creates the rows and key fields, has already been completed in this example and we are looking at the Update operation.

This is why the input nodes mapped are Order_Lines and Purchase_Order_Line. The required rows have been created and are pending the update to insert the order lines.

pic7

Above: The product of the data merge is mapped from the interleaved merge looping function to the Purchase_Order_Line node in the tool input schema.

pic8

Above: You can see that in this example there are several schemas defined as message outputs. This is because this operation has a multi-operation definition. Multiple operations are covered in other Knowledge base articles.

The Interleaved merge looping function: In summary

A loop is a function that iterates through all the specified elements in a set or array. The iteration is repeated until all the conditions specified for the looping have been met. Looping may affect the flow of data or even the structure of the data it acts on.

TaskCentre provides three looping functions in the data transformation component to carry out various kinds of looping: simple, split-by-type and interleaved merge.

Interleaved merge looping takes data from two or more source nodes and merges it to create one occurrence in the mapped destination node. It merges data items from each source node sequentially, so that the first items from all the nodes are combined in the destination node, then the second items, the third and so on, until all occurrences of data have been merged.

You access the function in the Functions pane of the DTC. To apply a looping to a data transformation, you drag an instance of the function to the mappings pane. You then add connectors between it and nodes or elements in the data input and tool input panes. You can also open the function’s dialog box and check or set various options there.

Links to related DTC articles