How to create a new column with maximum date from a set of rows?

[netucxsvgossy]

Hi. I'm new in Talend and i'm trying to set a new column with maximum date from a set of rows. Here is an example.

im reading a file in the format below:

Value | Date

A | 01-10-2021

B | 05-11-2021

C | 04-03-2022

D | 30-08-2021

E | 01-10-2020

And i want an output file like this:

Value | Date | Max_date

A | 01-10-2021 | 04-03-2022

B | 05-11-2021 | 04-03-2022

C | 04-03-2022 | 04-03-2022

D | 30-08-2021 | 04-03-2022

E | 01-10-2020 | 04-03-2022

can someone help me?

[Anonymous · Accepted solution]

You need to split the process into two steps:

1- Read the input data, group the input data and get the max date,

tFileInputDelimited--tMap--tAggregateRow--tJavaRow

tMap, add a new column called "group_col" with string type in the output table, set a fixed value eg: "1", this column will be used as group element on tAggregateRow,

tAggregateRow: group by "group_col" column.

tjavaRow: store the max date to global variable, eg:

globalMap.put("max_date",input_row.columnName);

2- Read the input data again, link it to a tMap and add a new column called "Max_date" in the output table, set its expression as:

(java.util.Date)globalMap.put("max_date",input_row.columnName)

Please try and let me know if you have any questions.

Regards

Shong