Six Stars

Filter the Most Recent Records

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!

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: Filter the Most Recent Records

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

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
7 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Filter the Most Recent Records

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

TRF
Six Stars

Re: Filter the Most Recent Records

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"

 

 

Twelve Stars TRF
Twelve Stars

Re: Filter the Most Recent Records

What is the datatype?

TRF
Six Stars

Re: Filter the Most Recent Records

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!

Twelve Stars TRF
Twelve Stars

Re: Filter the Most Recent Records

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

TRF
Six Stars

Re: Filter the Most Recent Records

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

Community Manager

Re: Filter the Most Recent Records

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

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business