topic Grouping the rating based on duplicate IDs in QlikView

Grouping the rating based on duplicate IDs

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

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

Re: Grouping the rating based on duplicate IDs

Saravanan_Desingh — Wed, 12 Aug 2020 02:50:04 GMT

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;

Re: Grouping the rating based on duplicate IDs

Saravanan_Desingh — Wed, 12 Aug 2020 02:50:59 GMT

Output.