Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Pls help me with the script to get the below expected result considering the below table is already loaded.
I have table with following columns (and values in rows):
ID Sign
AA X
DD Y
CC H
MM K
MM Z
AA Y
LL S
AA Z
DD Z
Condition:
If we have duplicate ID the record should be filtered against field [Sign] = 'Z',
If no duplicate records pick that row
Expected output:
ID Sign
AA Z
CC H
DD Z
LL S
MM Z
Thanks in Advance
May be :
Data:
load * inline [
ID,Sign
AA,X
DD,Y
CC,H
MM,K
MM,Z
AA,Y
LL,S
AA,Z
DD,Z
];
output:
noconcatenate
load ID,if(count(Sign)>1,'Z',MaxString(Sign)) as Sign resident Data group by ID;
drop table Data;
output :
One solution is.
tab1:
load * inline [
ID,Sign
AA,X
DD,Y
CC,H
MM,K
MM,Z
AA,Y
LL,S
AA,Z
DD,Z
];
Left Join(tab1)
LOAD ID, Count(ID) As Cnt
Resident tab1
Group By ID
;
tab2:
LOAD ID, Sign
Resident tab1
Where Cnt=1 Or (Cnt>1 And Sign='Z');
Drop Table tab1;
You received two different solutions to your post, we would greatly appreciate it if you would close the thread if one of those posts helped by using the Accept as Solution button on the post(s) that helped. If you did something different, you can post that and then use the button on that, and if you still have questions, please leave an update with the information you still need.
Regards,
Brett