topic Sort data based on value in Talend Studio

Sort data based on value

jolio007 — Mon, 29 Apr 2019 13:43:18 GMT

I have a table that contain duplicated ids. I want to look at all the values with the same id, if all the values are positive, I only want to keep the smallest value if the all the values are negative, I want to keep the biggest value. Essentially leaving me without duplicates.
Example :

if I start with this :

id	value
23	56
23	100
89	56
89	45
15	-5
15	50
15	100

At the end I want this :

id	value
23	56
89	45
15	100

If anyone has any idea on how I could achieve this, it'd help me a lot

thank you in advance !

Re: Sort data based on value

akumar2301 — Mon, 29 Apr 2019 14:07:22 GMT

Step 1 ) tAgreegateRow

Get min and Max for each ID

so output will be

23;56;100

89;45;56

15;-5;100

step 2 ) tjavarow or tMap : if Min is -Ve , get Max at value otherwise Min.

output_row.id = input_row.id;
output_row.value = input_row.min >= 0 ? input_row.min : input_row.max ;

Re: Sort data based on value

jolio007 — Mon, 29 Apr 2019 16:29:35 GMT

How would you do something similar but this time with a column in between like so

id	id2	value
28	98	-120
28	98	-100
28	99	20
28	99	-100
26	96	-100
26	96	-100
26	97	50
26	97	150
26	97	50

I want to say that if the first id (id) has positive value, it needs to find the smallest value in the for the group id and id2. If it's less than 0, I want it to find the biggest number within that group as well

id	id2	value
28	98	-100
28	99	20
26	96	-100
26	97	150

Thanks for you help