Sort data based on value

Highlighted
Five Stars

Sort data based on value

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 : 

idvalue
2356
23100
8956
8945
15-5
1550
15100

At the end I want this : 

id value
2356
8945
15100

 

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

 

thank you in advance ! 


Accepted Solutions
Eleven Stars

Re: Sort data based on value

 

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 ;

Regards
Abhishek KUMAR

All Replies
Eleven Stars

Re: Sort data based on value

 

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 ;

Regards
Abhishek KUMAR
Five Stars

Re: Sort data based on value

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

idid2value 
2898-120
2898-100
289920
2899-100
2696-100
2696-100
269750
2697150
269750

 

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 id2value
2898-100
289920
2696-100
2697150

 

Thanks for you help 

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 2

Part 2 of a series on Context Variables

Blog

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