RANK in script

[#1]

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

[Anonymous]

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;

[maxgro · Accepted solution]

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;

[hugmarcel]

Hi

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