topic Re: How to compare two rows based on values in a third column in Talend Studio

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

Anonymous — Thu, 25 Apr 2019 12:17:41 GMT

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.

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

akumar2301 — Thu, 25 Apr 2019 12:56:18 GMT

One of Solution is to use tAggregateRow

Group by on

Customer Number

ProductCode

Get Max of Column Indicator.

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

Anonymous — Thu, 25 Apr 2019 14:16:56 GMT

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?

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

akumar2301 — Thu, 25 Apr 2019 14:26:16 GMT

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

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

Anonymous — Thu, 25 Apr 2019 15:06:39 GMT

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.