Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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