Pick the record which has higher negative value

[tanvir_khan]

Hi guys,

happy new year!

I need to pick IDs from a table for which negative Score is than positive score. The info for department will show for corresponding negative score.

Input
ID|Score|Department
1001|900|A
1001|-1200|B
1003|50|A
1004|200|B
1004|-300|A
1005|-400|B
1006|800|A
1006|-500|A

Output
ID|Score|Department
1001|-1200|B
1004|-300|A

1005|-400|B

can anyone please let me know how can do it in alteryx?

thanks!

[Qiu]

@tanvir_khan 
Hope this is what you need.

[tanvir_khan]

@Qiu thanks a lot.

One more thing which I didn't cover in my example for simplicity, there can be more than two records.

For example - earlier there were two records for 1001, -1200 was higher than 900 but if there is one more record i.e. 400 then 1001 will be eliminated. 900+400 = 1300 which is 100 more than 1200. Similarly there can be multiple negative records all well against an ID and I need to capture all the negative records for corresponding ID.

Can you please suggest what can be done under such circumstances?

ID|Score|Department

1001|900|A
1001|-1200|B

1001|400|B

1003|50|A
1004|200|B
1004|-300|A
1005|-400|B
1006|800|A
1006|-500|A

thanks!

[Qiu · Accepted solution]

@tanvir_khan 
Feel like I need two accept mark now😁

[tanvir_khan]

@Qiu no, 1 point should be deducted from me as I couldn't clear it at the beginning.

btw thanks mate, though my problem is further complicated but got an idea from your explanation and that's sufficient for me.