Select from duplicate rows under condition

[Vichuf1]

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

[Taoufiq_Zarra]

@Vichuf1 

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 :

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

[Brett_Bleess]

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