Grouping the rating based on duplicate IDs

[prathu]

Hi , can you please help group the ratings and amount which are linked to duplicate IDs

ID	Ratings	Amount
1	2A	200
1	2B	300
2	3A	400
2	4B	300
3	5A	500
4	6A	800
4	7B	200
5	8A	400
6	9A	100

Desired Output

Ratings	Amount
2A	200
2B	300
3A	400
4B	300
6A	800
7B	200

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD * INLINE [
    ID, Ratings, Amount
    1, 2A, 200
    1, 2B, 300
    2, 3A, 400
    2, 4B, 300
    3, 5A, 500
    4, 6A, 800
    4, 7B, 200
    5, 8A, 400
    6, 9A, 100
];

Right Join(tab1)
LOAD *
Where Cnt > 1;
LOAD ID, Count(ID) As Cnt
Resident tab1
Group By ID;

Drop Field Cnt;

[Saravanan_Desingh]

Output.