topic Re: Ranking in the script in QlikView

Ranking in the script

arsenal1983 — Thu, 01 Sep 2016 08:26:12 GMT

Hello,

I have such data set:

LOAD

INLINE

[

MONTH, SALESMAN, SALES_VALUE

201501, BEN, 10

201501, ANN, 20

201501, JAC, 5

201502, BEN, 100

201502, ANN, 20

201502, JAC, 50

201503, BEN, 5

201503, ANN, 20

201503, JAC, 1

];

I need to rank the salesman in each month (from the highest sales value). In result I expect the table as below:

MONTH	SALESMAN	SALES_VALUE	RANKING
201501	BEN	10	2
201501	ANN	20	1
201501	JAC	5	3
201502	BEN	100	1
201502	ANN	20	3
201502	JAC	50	2
201503	BEN	5	2
201503	ANN	20	1
201503	JAC	1	3

Any ideas?

Re: Ranking in the script

Or — Thu, 01 Sep 2016 08:29:10 GMT

Group by month / salesman, sum the results, and use row numbers for ranking.

Better yet, try this resource: rank in script

Re: Ranking in the script

sunny_talwar — Thu, 01 Sep 2016 21:35:50 GMT

May be try this

Table:

LOAD * INLINE

[

MONTH, SALESMAN, SALES_VALUE

201501, BEN, 10

201501, ANN, 20

201501, JAC, 5

201502, BEN, 100

201502, ANN, 20

201502, JAC, 50

201503, BEN, 5

201503, ANN, 20

201503, JAC, 1

];

FinalTable:

LOAD *,

AutoNumber(SALES_VALUE, MONTH) as RANK

Resident Table

Order By MONTH, SALES_VALUE desc;

DROP Table Table;

Re: Ranking in the script

FlyingCheesehead — Mon, 06 Feb 2023 18:05:02 GMT

This is, overall, a good solution, but it does not take into account any duplicate values.

For example, if I have the following sales data:

Salesperson,Sales
Arlene,100
Bob,50
Charlene,80
David,50
Erin,95
Flynn,35
Gertrude,10

Doing the autonumber will result in rankings that look like this:

Rank Sales Salesperson
1    100   Arlene
2     95   Erin
3     80   Charlene
4     50   Bob
4     50   David
5     35   Flynn
6     10   Gertrude

That then implies that Flynn is the 5th-best seller, when in reality he's 6th. This isn't generally a big deal with such a small dataset, but with large datasets that have lots of duplicates, the farther down the list you go, the more wrong the rankings get.

Is there a better solution? Why doesn't Qlik allow the use of Rank in the script?

Re: Ranking in the script

KD_ — Thu, 11 Jan 2024 04:14:38 GMT

I think since we now have Window Functions, there should be some capability there. But I am not 100% sure since I have not have the chance to use them in Qlik Sense or QlikView

Re: Ranking in the script

Ahidhar — Thu, 11 Jan 2024 04:58:16 GMT

you can use windows function to get rank like this

load *,window(recno(),'Desc',Sales) as Rank;
LOAD*INLINE
[
Salesperson,Sales
Arlene,100
Bob,50
Charlene,80
David,50
Erin,95
Flynn,35
Gertrude,10
];