# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

## First 3 values of field by each ID

Hello,

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

Thanks!

1 Solution

Accepted Solutions

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

``````let vRank =3;

Data:
ID,
Country,
Product
FROM table;

for i=1 to \$(vRank)

Left Join(Data)
Country as LID_\$(i),
Product as [L\$(i)]
Resident Data
where Rank=\$(i);

NEXT

4 Replies

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

MVP

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

Raw:
*,
if(Rank <= 3, ID, null()) as LinkId
;
Country,
Product,
AutoNumber(Country, ID) as Rank
FROM

(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Countries:
Generic
Resident Raw
Where Rank <= 3
;
Products:
Generic
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

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

``````let vRank =3;

Data:
ID,
Country,
Product
FROM table;

for i=1 to \$(vRank)

Left Join(Data)
Country as LID_\$(i),
Product as [L\$(i)]
Resident Data
where Rank=\$(i);

NEXT