Need to eliminate duplicates by order

[karan_kn]

We need to create output as below screenshot. It's bit difficult to explain, so mention in that in screenshot.

LOAD * INLINE [

    ID, Item, Brand

    1, a2, AAA

    1, a2, BBB

    1, b4, BBB

    1, a2, CCC

    1, b4, CCC

    1, c7, CCC

    2, c2, AAA

    2, C3, AAA

    3, E1, AAA

    3, E2, BBB

    3, E1, CCC

    3, E2, DDD

    4, S1, AAA

    4, S1, AAA

    4, S1, BBB

    4, S3, CCC

    4, S4, DDD

];

To keep top Brand and eliminate lower Brand with Same ID

[vishsaggi]

Try this may be:

OrderData:

LOAD * INLINE [

    ID, Item, Brand

    1, a2, AAA

    1, a2, BBB

    1, b4, BBB

    1, a2, CCC

    1, b4, CCC

    1, c7, CCC

    2, c2, AAA

    2, C3, AAA

    3, E1, AAA

    3, E2, BBB

    3, E1, CCC

    3, E2, DDD

    4, S1, AAA

    4, S1, AAA

    4, S1, BBB

    4, S3, CCC

    4, S4, DDD

];

NoConcatenate

LOAD *

WHERE AutoNum = 1;

LOad *, Autonumber(ID&Item&Brand, Item&ID) AS AutoNum

Resident OrderData

Order By ID, Item,Brand;

Drop table OrderData;

[trdandamudi]

One other way as attached:

[karan_kn]

Thanks for the response, but it wont work, if the brand with Names instead of Autonumbers.

[rajaxavier]

Tab:

LOAD * INLINE [

ID, Item, Brand

1, a2, AAA

1, a2, BBB

1, b4, BBB

1, a2, CCC

1, b4, CCC

1, c7, CCC

2, c2, AAA

2, C3, AAA

3, E1, AAA

3, E2, BBB

3, E1, CCC

3, E2, DDD

4, S1, AAA

4, S1, AAA

4, S1, BBB

4, S3, CCC

4, S4, DDD

];
NoConcatenate

Tab1:
Load
ID,
Item,
FirstValue(Brand) as Brand
Resident Tab Group by ID,Item;

Drop Table Tab;