How to group and only return single row for each group

Four Stars

How to group and only return single row for each group

I need to group and then filter out certain rows of data within each group and I am struggling on how to achieve this.

 

Here is some data (columns on top):

 

Id, lastmodifieddate, relatedto,address

1,2017-1-1,X,address1

2, 2015-1-1,Y,address2

3, 2017-6-6,X,address3

4, 2015-1-1,Z,address4

5, 2014-1-1,Y,address5

 

In this case, I want to group the records by relatedto and then only return the record with the most recent lastmodifieddate. So, for the example above, I want the results to only return 3 records as shown below:

 

Id, lastmodifieddaterelatedto,address

2, 2015-1-1,Y,address2

3, 2017-6-6,X,address3

4, 2015-1-1,Z,address4

 

I used tAggregateRow with group by relatedto and max operation on lastmodified date to get the row. However, when I use the tLogRow component to log the output, I only see the relatedTo and lastmodified column, but the Id and address columns are null. 

 

How can I get the Id and address column to show the value for the row that has the latest lastmodified date for each relatedto?

 

I am new to Talend so be kind :-)

 

Thank you.

 

 

Tags (1)

Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

Re: How to group and only return single row for each group

Hi,

 

The simplest solution (here the input is from tFixedFlowInput for testing):

Capture.PNG

- tSortRow to order records on lastmodifieddate descending order

- tUniqRow to eliminate duplicate records on relatedto

- tSortRow to order records on id ascending order

That's all


TRF

All Replies
Fifteen Stars TRF
Fifteen Stars

Re: How to group and only return single row for each group

Hi,

 

The simplest solution (here the input is from tFixedFlowInput for testing):

Capture.PNG

- tSortRow to order records on lastmodifieddate descending order

- tUniqRow to eliminate duplicate records on relatedto

- tSortRow to order records on id ascending order

That's all


TRF
Four Stars

Re: How to group and only return single row for each group

Thank you TRF.

I was missing the tUniqRow piece.

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

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now