topic Re: First 3 values of field by each ID in Visualization and Usability

First 3 values of field by each ID

bhargavikn — Tue, 24 Aug 2021 13:10:03 GMT

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!

Re: First 3 values of field by each ID

Kushal_Chawda — Tue, 24 Aug 2021 15:07:41 GMT

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

Re: First 3 values of field by each ID

rwunderlich — Tue, 24 Aug 2021 15:34:35 GMT

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

Re: First 3 values of field by each ID

Kushal_Chawda — Tue, 24 Aug 2021 20:17:43 GMT

@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;

Re: First 3 values of field by each ID

bhargavikn — Wed, 25 Aug 2021 09:39:15 GMT

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