topic Select from duplicate rows under condition in QlikView

Select from duplicate rows under condition

Vichuf1 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Select from duplicate rows under condition

Taoufiq_Zarra — Mon, 08 Jun 2020 20:56:38 GMT

@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 :

Re: Select from duplicate rows under condition

Saravanan_Desingh — Mon, 08 Jun 2020 21:40:00 GMT

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;

Re: Select from duplicate rows under condition

Saravanan_Desingh — Mon, 08 Jun 2020 21:40:55 GMT

Re: Select from duplicate rows under condition

Brett_Bleess — Wed, 01 Jul 2020 19:15:46 GMT

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