Requirement :
If we have multiple source counterparty id for a counterparty name then
1) Check if trade name is same- if 'yes' then use the max id. If 'no' then follow below steps
a. If multiple trade names then pick the id from 'A' if 'A' trade name exists
b. If 'A' source name does not exits then use 'B' id if exists
c. If 'B' is not available then use 'C' id
d. If 'C' is not available then use 'D' id
sample date and desired result
Source CPTY ID CPTY NAME AMOUNT Trade Name
13682 ABC 1000 A
5412 ABC 1000 B
9621 ABC 1000 C
23657 ABC 1000 D
6542 XYZ 2000 B
963241 XYZ 2000 C
785321 XYZ 2000 D
13254 CDF 3000 A
23645 CDF 3000 A
Desired result
Source CPTY ID CPTY NAME AMOUNT Trade Name
13682 ABC 1000 A
6542 XYZ 2000 B
23645 CDF 3000 A
here is my effort :
if
([count source id]>1 and [count trade name]>1)
then
(if ([Trade Name]='A') then (Source Counterparty Identifier]) else if ([Trade Name]='B') then ([Source Counterparty Identifier]) else if ([Trade System Name]='C') then ([Source Counterparty Identifier]) else ([Source Counterparty Identifier]))
else ([max source id])
count source id =count(distinct source counterparty id for cpty name)
count trade name= count(distinct trade name for cpty name)
max source id =maximum(source id for cpty name)
but I am not able to get desired results anyone help me out from this?