topic RANK in script in QlikView

RANK in script

Thu, 24 Jul 2014 00:43:48 GMT

Hi All,

I have a requirement to rank Sales figure. I can rank it in UI but I wanted to move the rank to script for performance reasons.

So say I have a table like this

LOAD *

INLINE [

SalesDates, SalesFigure

11/02/2013, 200

15/02/2013, 205

16/02/2013, 300

20/02/2013, 105

25/02/2013, 45

22/02/2013, 99

24/06/2013, 600

25/06/2013, 6580

28/06/2013, 900

30/06/2013,105

]

How can I rank it in script?

Thanks in advance

Re: RANK in script

Anonymous — Thu, 24 Jul 2014 00:56:35 GMT

Test:

LOAD *

INLINE [

SalesDates, SalesFigure

11/02/2013, 200

15/02/2013, 205

16/02/2013, 300

20/02/2013, 105

25/02/2013, 45

22/02/2013, 99

24/06/2013, 600

25/06/2013, 6580

28/06/2013, 900

30/06/2013,105

];

NoConcatenate

Table:

LOAD

ROWNO()as Rank;

LOAD

SalesDates,

SalesFigure

Resident Test Order by SalesFigure asc; // or desc

Drop Table Test;

Re: RANK in script

maxgro — Thu, 24 Jul 2014 02:03:29 GMT

rank for 105 is 3 on both records

tmp:

LOAD *

INLINE [

SalesDates, SalesFigure

11/02/2013, 200

15/02/2013, 205

16/02/2013, 300

20/02/2013, 105

25/02/2013, 45

22/02/2013, 99

24/06/2013, 600

25/06/2013, 6580

28/06/2013, 900

30/06/2013,105

];

final:

load *,

if(Peek(SalesFigure)=SalesFigure, Peek(rank), alt(Peek(rank),0)+1) as rank

Resident tmp order by SalesFigure;

DROP Table tmp;

Re: RANK in script

hugmarcel — Sun, 15 Nov 2015 11:15:11 GMT

see my example file rank.qvw.

- it contains a sub providing flexible ranking mechanisms,
such as ranking by autonumber or by row().
- ranks can be split into half ranks for identical values
(e.g. rank 11,12,13 -> 12, 12, 12)

https://community.qlik.com/docs/DOC-13949

Regards - Marcel