Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
One other way as attached:
Thanks for the response, but it wont work, if the brand with Names instead of Autonumbers.
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;