Problem

You often have repeating structures in the input data that you want to evaluate. In a simple case, for example, this could be a field value that contains a price and you want to determine the total price. However, you may also want to group the data in a large order according to certain criteria. Of course, this can be solved using existing on-board tools in the target structure, but this can sometimes lead to complicated constructs that are not always easy to create and maintain. That's why there is another option for solving tasks of this kind more conveniently and also making the evaluated data available in the source structure, so that this data is already available in the mapping process instead of only in constructs in the target structure.

But let's be a little more specific. Let's assume the following source structure.

We read the following file into this source structure.

This gives us the node row four times (when using appropriate match codes).

Aggregate functions and special nodes

We would now like to summarise the values of the fields field_1 and field_2 for all four iterations (e.g. calculate the sum).

To do this, select the node row (1) in the source structure and select option Aggregate functions (2) in the context menu .

A new dialogue will then open.

In the dialogue, select the aggregate function sum() for the two fields field_1 and field_2 (3) and click on (4). We can ignore all other settings for the time being.

As result, we get the following structures.

The special node (5) was created in the source structure. This node is used to receive the aggregated field values. Note: You can use (16) to control whether node (5) should be emptied before the data is inserted. Note: You can use the default value in (17). If for some reason you want to adjust the field names in (5), you can use the value Copy as sequence in (17) instead, in which case the data is not filled using the same field names, but using the order of the fields.

Node (6) was created in the target structure. This is used to aggregate the field values and is iterated as often as node row occurs in the source tree (→ path). A special function is executed on field (6) that summarizes the field values (as defined in the dialogue) in an internal map (aggregate map).

In the field of the target structure node (7), this aggregate map is then copied into the source structure special node (5) and is then available there for further mapping from this point onward. You may already be familiar with this in a similar form from functionality adding special source structure node for lists/maps. You can map the content of the special node (5) as usual. You can also copy and map it manually into the target structure (via the toolbar at the top of phase 3 or the context menu) or set checkbox (8) in the dialogue, in which case this is done automatically.

Note: If there are already nodes in the target structure, you can use (15) to control where nodes (6) and (7) are inserted.

Now let's take a look at the result of the mapping test. The target structure field field_1 contains the value 4, i.e. the summed values of the multiple source structure field field_1 (1+1+1+1=4). The same applies to target structure field field_2 (2+2+2+2=8).

You do not need to make any further settings here. Advanced users can take a look at the functions create aggregated map() and copy aggregated map to source tree() used here, but this is normally not necessary.

You do not have to work directly with the aggregate map, nor can you do so with the 'normal' map functions. Please also note that the content of the aggregate map is not displayed in a mapping test. The aggregate map is primarily an internal data structure.

However, you can use function convert aggregated map() to convert the aggregate map into a 'normal' map, which is then visible in a mapping test and can also be manipulated normally. In addition, you could then pass the 'normal' map on to other profiles in serialised form. The aggregate map can be deleted with function clear aggregated map().

Available aggregate functions:

first() - The first occurring value of the source structure field.

min() - The smallest occurring value of the source structure field.

max() - The largest occurring value of the source structure field.

sum() - The sum of all values of the source structure field.

avg() - The average of all values of the source structure field.

count() - How many values of the source structure field do occur.

Groupings

Let's take a look at field field_3. The values a and b appear here. We now want to perform a grouping using these values, i.e. we want to group together all data rows that have the same value in field_3.

To do this, check option (9) in the GroupBy column for field field_3 in the dialogue. Note: The value first() in column Aggregate is automatically set here and is only required for internal purposes. You can ignore this value.

The grouping takes place internally in the aggregate map using function create aggregated map(a,b,c). No manual configuration is necessary here either.

We see that this results in two iterations of node _GroupBy_row. In the first iteration (10) all data rows with the value a on field_3 are grouped together (2 rows) and in the second iteration (11) all rows with the value b on field_3 are grouped together.

Using dialogue again

If you open the dialogue again via the context menu, there are several options, depending on where you call up the dialogue.

Aggregate map