Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
smic
Partner
Partner

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