topic Re: Filter the Most Recent Records in Talend Studio

Filter the Most Recent Records

Anonymous — Tue, 30 May 2017 18:15:53 GMT

I have an output from the component tParseRecordSet which has 5 columns, let's say A, B, C, D, E.

Column E is the updated time of each record.

Now I want to get the most update D for each group (group by A, B)

Question: what component should I use to achieve this? Because the data is not in the database, I can't directly use SQL statements to do it.

Any help is greatly appreciated. Thanks!

Re: Filter the Most Recent Records

TRF — Tue, 30 May 2017 18:52:36 GMT

You should use tAggregateRow, group by fields A and B, then function Max o' field D.

Re: Filter the Most Recent Records

Anonymous — Tue, 30 May 2017 18:54:39 GMT

Thanks for your reply. I tried same way as you said... The following is the error message:

Unparseable date: "2017-04-03 21:08:37.0"
Unparseable date: "2017-03-29 10:20:20.0"
Unparseable date: "2017-04-03 21:08:37.0"
Unparseable date: "2017-03-29 10:20:20.0"
Unparseable date: "2017-04-03 21:08:37.0"
Unparseable date: "2017-03-29 10:20:20.0"

Re: Filter the Most Recent Records

TRF — Tue, 30 May 2017 19:10:34 GMT

What is the datatype?

Re: Filter the Most Recent Records

Anonymous — Tue, 30 May 2017 19:13:49 GMT

The datatype of Field D in my case is timestamp, but as it's from the previous component tParseRecordSet, so typr of field D in tParseRecordSet is Object. Thanks!

Re: Filter the Most Recent Records

TRF — Tue, 30 May 2017 20:00:00 GMT

OK, so you need 1st to convert as date or string (as soon as the format starts with year, alphabetic order is also possible).

Re: Filter the Most Recent Records

Anonymous — Tue, 30 May 2017 20:35:20 GMT

just convert it in the schema or I need to add another component?

Re: Filter the Most Recent Records

Anonymous — Thu, 01 Jun 2017 03:30:54 GMT

Hi FFUL
On tParseRecordSet, define D column as Date type, and set its pattern as "yyyy-MM-dd HH:mm:ss.S".

Regards
Shong