topic Remove duplicat ID's using load statement in QlikView

Remove duplicat ID's using load statement

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

Remove duplicate ID's with same Item, to keep only whichever higher Brand. Please refer screenshot for understanding the logic and requirement. Final output will be stored as QVD. Thanks

Re: Remove duplicat ID's using load statement

rubenmarin — Mon, 24 Sep 2018 13:32:42 GMT

Hi Karan, sorting by Id, Item and Brand descending you can pick the first record for each ID and Item combination, like:

LOAD ID, Item, Brand
Resident TableName
Where ID<>Previous(ID) or Item<>Previous(Item)
Order By ID, Item, Brand desc;

DROP Table TableName;

Re: Remove duplicat ID's using load statement

karan_kn — Mon, 24 Sep 2018 13:43:01 GMT

Inline text for reference:

TableName:

LOAD * INLINE [

ID, Item, Brand

1, a2, Top Brand

1, a2, Medium Brand

1, b4, Medium Brand

1, a2, Low Brand

1, b4, Low Brand

1, c7, Low Brand

2, c2, Top Brand

2, C3, Top Brand

3, E1, Top Brand

3, E2, Medium Brand

3, E1, Low Brand

3, E2, Very Low Brand

4, S1, Top Brand

4, S1, Medium Brand

4, S3, Low Brand

4, S4, Very Low Brand

];

Re: Remove duplicat ID's using load statement

rubenmarin — Mon, 24 Sep 2018 13:45:43 GMT

PFA.

Re: Remove duplicat ID's using load statement

karan_kn — Mon, 24 Sep 2018 13:47:56 GMT

Thanks for the reply, but the logic not working.

Ex: for ID=3 should be Medium Brand (higher one)

Re: Remove duplicat ID's using load statement

rubenmarin — Mon, 24 Sep 2018 13:53:46 GMT

Ok I overlooked the 'V'... then you can apply a number to each brand to set the numerical order, in attached example is assigned using a mapping.