How to compare two rows based on values in a third column

[Anonymous]

Hi All,

Currently I am struggling with a seemingly easy challenge, but I can't seem to figure out how to do it other than some - not so neat - solutions.

In my Input I have a customer number (string), a product code (string), and an indicator (string). example:

Customer Number	ProductCode	Indicator
4880	2929	0
4880	2929	1
5579	2929	0

As you can see for customer number 4880 the productcode is the same in row 1 and row 2, but the indicator is different.

My preferred outcome would be that Talend would only select the row where the indicator = 1. However for customer number 5579 there is only one row and the indicator is 0. In that case I also want to have that row in my output. Therefore my preferred output would be:

Customer Number	ProductCode	Indicator
4880	2929	1
5579	2929	0

Is this something you can do easily, preferably in one tMap, instead of making multiple outputs and comparing them together.

ps. (input.Indicator).equals("1") will not work as for 5579 there is only one row with indicator 0 which I want to keep as well. 

Thanks in advance.

[akumar2301 · Accepted solution]

One of Solution is to use tAggregateRow

Group by on 

Customer Number

ProductCode

Get Max of Column Indicator.

[Anonymous]

Hi Abishek,

This indeed works - thanks for the solution.

However I am now facing the issue that the output is only containing the grouped by columns. 

Next to Customer Number and ProductCode I also have many other columns which I expected to transfer.

I did selected Edit Schema to transfer all from input to output of course as can be seen below.

The output I receive is now as expected but almost all columns are empty.

Am I missing something?

[akumar2301]

I am afraid , all the columns needs to be added in Group By.

[Anonymous]

Unfortunately, yes, but then in my case its not working anymore. What I did was using the tAggregateRow and putting the output in a tHashOutput. In the next subjob I use the tHashOutput as my main input (quite empty), and refill the missing values based on the unique combination of CustomerNumber and ProductCode. So for me it works now.