topic Re: Rank by sum and order in load script in QlikView

Rank by sum and order in load script

smic — Tue, 25 Aug 2020 19:02:16 GMT

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_ID	Player_Id	Sum(Score)	rank_score	Sum(km)	rank_km
1	1	11	3	15	3
1	2	23	1	12	4
1	3	12	2	17	2
1	4	11	3	18	1
2	1	17	4	19	2
2	2	19	3	19	2
2	3	22	2	21	1
2	4	23	1	21	1
2	5	14	5	15	3
3	2	22	1	17	1
3	3	12	2	16	2

Regards !

Re: Rank by sum and order in load script

jerem1234 — Wed, 16 Sep 2020 19:41:00 GMT

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!

Re: Rank by sum and order in load script

Kushal_Chawda — Wed, 16 Sep 2020 22:40:33 GMT

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