Write first grouping value to database

Translated submission in the German forum. You can read the original entry here .

Hi,

Unfortunately we have a problem and I can’t find information about this on the website.
My input data has records sorted on the key column [DEVICE] Several instances of the same key can occur, and in this case an additional field defines the sequence [EXTRA].

Output to the database should be a single record containing a consolidation of the data.
We have grouped the data on the [DEVICE] column and thereby obtain a sum of the numeric column [COST].

In the output data, only the text in the [DESCRIPTION] column where the [EXTRA] column is empty should be passed on. This represents the heading information for the group, so to say.

We were not able to implement it ourselves.
Can you advise whether this is possible?

Example of actual INPUT data
Device;Extra;Description;Cost
10001;;Headline 1;1000
10001;E1;Extra 1;500
10001;E2;Extra 2;500
10002;;Headline 2;2000
10002;E1;Extra 1;500
10002;E2;Extra 2;500

Example of OUTPUT desired
Device;Description;Cost
10001;Headline 1;2,000.00 €
10002;Headline 2;3,000.00 €

Hi,

The problem with this is that with a grouping performed by the GroupBy Heater normally overwrites the string data for a group from each row in sequence and so any text in the final row of a group effectively determines the output. If you wish to retain the text data from an alternative row in the group instead, it is necessary to choose this with an IF-THEN-ELSE condition .

I have constructed a short example to process your data, see the attached grouping.zip.

Please note that the IF-THEN-ELSE Heater in this example only has 2 input parameters.

1x the value compared by the condition
1x the value that will be passed on if the condition is met

In this case the [EXTRA] column is compared with an empty string; in your case this is only true for the first row in each group, where the [EXTRA] column is always empty.

Only when the condition is met is the [DESCRIPTION] column written to the output field.
When the condition is not met, nothing is passed on by the IF-THEN-ELSE Heater, because only one output parameter is defined, hence the value in the first row of each group is not overwritten.