Ranking in the script

[arsenal1983]

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?

[Or]

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

Better yet, try this resource: rank in script

[sunny_talwar · Accepted solution]

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;

[FlyingCheesehead]

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?

[KD_]

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

[Ahidhar]

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