Remove duplicat ID's using load statement

[karan_kn]

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

[rubenmarin1]

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;

[karan_kn]

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, Top Brand

    4, S1, Medium Brand

    4, S3, Low  Brand

    4, S4, Very Low  Brand

];

[rubenmarin1]

PFA.

[karan_kn]

Thanks for the reply, but the logic not working.

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

[rubenmarin1 · Accepted solution]

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.