Pick the record which has higher negative value

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!

Developer

Developer Tools

Input

Accepted answers

Qiu

@tanvir_khan
Feel like I need two accept mark now😁

0121-tanvir_khan.yxmd

All comments

Qiu

@tanvir_khan
Hope this is what you need.

0121-tanvir_khan.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703