Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
smic
Partner - Contributor II
Partner - Contributor II

Rank by sum and order in load script

Hi,

Hello, I try to make a rank, by Test and by player according to several measures (independent of each other).

I almost got with the AutoNumber function, however I can't seem to reset the values ​​for each measurement so the rank doesn't work ...

Anyone have any idea how I can get this kind of rank in my loading script please?

 

Test_IDPlayer_IdSum(Score)rank_scoreSum(km)rank_km
11113153
12231124
13122172
14113181
21174192
22193192
23222211
24231211
25145153
32221171
33122162

 

Regards !

2 Replies
jerem1234
Specialist II
Specialist II

Hi Smic,

You can try this using peek:

Data:
Load * Inline [
Test_ID, Player_Id, Score, km
1,1,11,15
1,2,23,12
1,3,12,17
1,4,11,18
2,1,17,19
2,2,19,19
2,3,22,21
2,4,23,21
2,5,14,15
3,2,22,17
3,3,12,16
];

Left Join(Data)
Load
Test_ID,
Player_Id,
if(Test_ID <> previous(Test_ID), 1,
if(Score <> Previous(Score), peek(Score_Rank)+1, peek(Score_Rank))) As Score_Rank
Resident Data
Order by Test_ID Asc, Score Desc;

Left Join(Data)
Load
Test_ID,
Player_Id,
if(Test_ID <> previous(Test_ID), 1,
if(km <> Previous(km), peek(Km_Rank)+1, peek(Km_Rank))) As Km_Rank
Resident Data
Order by Test_ID Asc, km Desc;

Hope this helps!

Kushal_Chawda

@smic  try below

Data:
Load * Inline [
Test_ID, Player_Id, Score, km
1,1,11,15
1,2,23,12
1,3,12,17
1,4,11,18
2,1,17,19
2,2,19,19
2,3,22,21
2,4,23,21
2,5,14,15
3,2,22,17
3,3,12,16
];

T1:
LOAD *,
AutoNumber(Score,'Score'&Test_ID) as Rank_score
Resident Data
Order by Test_ID,Score desc;

DROP Table Data;

T2:
LOAD *,
AutoNumber(km,'km'&Test_ID) as Rank_km
Resident T1
Order by Test_ID,km desc;

DROP Table T1;

Screenshot 2020-09-16 234004.png