Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
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