[resolved] how do do this with taggregaterow ?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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"

[Anonymous · Accepted solution]

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
                

[Anonymous]

Good idea.
thanks.