First 3 values of field by each ID

[bhargavikn]

Hello,

Please help with the solution for below query.

We have table like below:

ID	Country	Product
1	IND	P1
1	UAE	P2
1	USA	P2
1	AUS	P3
2	NZ	P1
2	IND	P4
3	AF	P5
3	AL	P6
3	KH	P3
4	KY	P7
4	CM	P8
4	CAN	P9
4	BOL	P4

we need to get top 3 values for each ID and this has to be in script (not chart expression).

The Output should be like below

ID	Country	Product	LID_1	L1	LID_2	L2	LID_3	L3
1	IND	P1	IND	P1	UAE	P2	USA	P2
1	UAE	P2	IND	P1	UAE	P2	USA	P2
1	USA	P2	IND	P1	UAE	P2	USA	P2
1	AUS	P3	Null	Null	Null	Null	Null	Null
2	NZ	P1	NZ	P1	IND	P4	Null	Null
2	IND	P4	NZ	P1	IND	P4	Null	Null
3	AF	P5	AF	P5	AL	P6	KH	P3
3	AL	P6	AF	P5	AL	P6	KH	P3
3	KH	P3	AF	P5	AL	P6	KH	P3
4	KY	P7	KY	P7	CM	P8	CAN	P9
4	CM	P8	KY	P7	CM	P8	CAN	P9
4	CAN	P9	KY	P7	CM	P8	CAN	P9
4	BOL	P4	Null	Null	Null	Null	Null	Null

Please help me with the script to get top 3 values for each ID. 

your help much appreciated.

Thanks!

[Kushal_Chawda]

@bhargavikn  how do you decide which top 3 values? Because for each id data could sort randomly.

[rwunderlich]

Assuming that "top" means first three in load order, try:

Raw:
LOAD
*,
if(Rank <= 3, ID, null()) as LinkId
;
LOAD ID,
     Country,
     Product,
     AutoNumber(Country, ID) as Rank
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/First-3-values-of-field-by-each-ID/td-p/1830804]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Countries:
Generic
Load LinkId, 'LID_' & Rank, Country
Resident Raw
Where Rank <= 3
;
Products:
Generic
Load LinkId, 'L' & Rank, Product
Resident Raw
Where Rank <= 3
;

For i = NoOfTables() - 1 to 0 step -1
Let vTable = TableName($(i));
IF (WildMatch('$(vTable)', 'Countries.*', 'Products.*')) THEN
Left Join(Raw) LOAD * Resident $(vTable);
Drop Table $(vTable);
ENDIF
Next i
Drop Field LinkId; 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[Kushal_Chawda · Accepted solution]

@bhargavikn  Assuming top 3 as first 3 records you can try below

let vRank =3;

Data:
LOAD *,
     div(Rank,$(vRank)+1) as LinkRank;
LOAD if(ID<>Previous(ID),1,rangesum(Peek(Rank),1)) as Rank
     ID,
     Country,
     Product
FROM table;

for i=1 to $(vRank)
 
Left Join(Data)
LOAD ID,
     LinkRank,
     Country as LID_$(i),
     Product as [L$(i)]
Resident Data
where Rank=$(i);

NEXT

drop field LinkRank;

[bhargavikn]

Both are working fine.. Thank you so much for your help  😃