topic Re: [resolved] how do do this with taggregaterow ? in Talend Studio

[resolved] how do do this with taggregaterow ?

Anonymous — Tue, 24 Nov 2015 10:42:07 GMT

Hi
My input file have 3 columns with id (name id1 id2 id3) and 1 column with a date
And i want in my output file :
- a group by id1 and id2
- and the id3 corresponding to the max date
The thing is that the id3 is not in date order, so i have to take the latest date and the id3 corresponding to that line.

how i do this with a taggregaterow ?

thanks for your help
Pierre

Re: [resolved] how do do this with taggregaterow ?

Anonymous — Thu, 26 Nov 2015 10:27:56 GMT

Hello Pierre
Can you show us an example with some real data? It will be helpful for us to understand your requirement.

Re: [resolved] how do do this with taggregaterow ?

Anonymous — Thu, 26 Nov 2015 10:39:43 GMT

For example as an input i have this :
ID1 | ID2 | ID3 | DATECREATION
21 | 1 | 101 | 20/11/2015
21 | 2 | 102 | 22/11/2015
52 | 3 | 109 | 24/11/2015
52 | 3 | 105 | 26/11/2015

the result expecting :

ID1 | ID2 | ID3 | CREATIONDATE
21 | 1 | 101 | 20/11/2015
21 | 2 | 102 | 22/11/2015
52 | 3 | 105 | 26/11/2015

For the 2 last lines i want to keep the line with the latest CREATIONDATE (so the ID3 105 ).

So i can use agregate but i cant say "i want the ID3 corresponding to the line with the latest CREATIONDATE"

Re: [resolved] how do do this with taggregaterow ?

Anonymous — Mon, 30 Nov 2015 06:42:50 GMT

Hi
Only read ID2 and DATECREATION columns, do an aggregation operation on these two fields with tAggregateRow, group by ID2 and select 'Max' function on DATECREATION column, after that, do a join with the source data based on DATECREATION column on a tMap, the job design looks like:
tFileinputDelimited--main--tAggregateRow--main--tMap---main---tLogRow
|
lookup
|
tFileinputDelimited

Re: [resolved] how do do this with taggregaterow ?

Anonymous — Mon, 30 Nov 2015 08:50:26 GMT

Good idea.
thanks.