How to make groups in an input and select a specific row in each of them in Talend?

Question

I am working on a Talend transformation process (we are using Talend 6.4). , and I don't know how to implement the current requirement.

I have an input consisting in :

• Two columns that are my group keys (Account and Product), but are not unique (the same Account x Product couple can happen in multiple rows)
• A criterion column (Contract end date), which will help me decide which row I want to keep for each group
• Some "tail" data that need to be passed to the following step of the processing (the contract number)

The rule to implement is:

• Keep only one record per group
• The selected record must be one with no end date or, if all have end date, with the biggest end date
• The selected record can be random in case there is a tie

See the transformation applying those rules on some dummy data:

I thought first to do the following:

1. sort by Account, Product, End_date (nulls first)
2. "select first" in each group

but I am not skilled enough to know whether the second transformation exists in Talend.

Regards,

Pierre

Answer 1

The answer from @MBDIA seem to be working, however I would like to share what we did to fulfill our requirement.

See our Talend process here:

The first tMap (tMap_3) acts like a tReplicate and a tMap, and sends:

• in the upper branch only the Account and Product references, that are then deduplicated by the tAggregateRow_1.
• in the lower branch all data and computed fields that enables us to take care of the case where the date is missing (instead of defaulting to 31/12/9999, we compute a flag (0 or 1) that we use in the sort step afterwards).

In the second part of the process, we first apply the sort to the whole data on Account, Product, Empty date flag (computed before), End date (desc) and use a second tMap to make a join on both branches (on Account x Product), only keeping First Match in order to keep the first record as per our requirement.

Answer 2

Very interesting Talend question. You need to create something like this job.

here a link to the zip file to import in your Talend