Qlik Community

New to Qlik Sense

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

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhargavikn
Contributor III
Contributor III

First 3 values of field by each ID

Hello,

Please help with the solution for below query.

We have table like below:

IDCountryProduct
1INDP1
1UAEP2
1USAP2
1AUSP3
2NZP1
2INDP4
3AFP5
3ALP6
3KHP3
4KYP7
4CMP8
4CANP9
4BOLP4

 

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

IDCountryProductLID_1L1LID_2L2LID_3L3
1INDP1INDP1UAEP2USAP2
1UAEP2INDP1UAEP2USAP2
1USAP2INDP1UAEP2USAP2
1AUSP3NullNullNullNullNullNull
2NZP1NZP1INDP4NullNull
2INDP4NZP1INDP4NullNull
3AFP5AFP5ALP6KHP3
3ALP6AFP5ALP6KHP3
3KHP3AFP5ALP6KHP3
4KYP7KYP7CMP8CANP9
4CMP8KYP7CMP8CANP9
4CANP9KYP7CMP8CANP9
4BOLP4NullNullNullNullNullNull

 

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

your help much appreciated.

Thanks!

1 Solution

Accepted Solutions
Kushal_Chawda

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

 

View solution in original post

4 Replies
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

@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
Contributor III
Contributor III
Author

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