From Thursday, July, 9, 3:00 PM Pacific,
our Community site will be in
read-only mode
through Sunday, July 12th.
Thank you for your patience.

How to group and only return single row for each group

Highlighted
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
Highlighted
Sixteen Stars
Sixteen 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

View solution in original post


All Replies
Highlighted
Sixteen Stars
Sixteen 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

View solution in original post

Highlighted
Four Stars

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

Thank you TRF.

I was missing the tUniqRow piece.

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog