Filter the Most Recent Records

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)

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

All Replies
Fifteen Stars TRF
Fifteen 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"

 

 

Fifteen Stars TRF
Fifteen 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!

Fifteen Stars TRF
Fifteen 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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download