How to get rank() functionality in Qlik code

[Naps_230]

Hi ,

I have sql data like.

SELECT period_id,
week_ending_date,
trimester_of_year_cal,
Rank()
OVER (
ORDER BY week_ending_date DESC) AS rnk
FROM $(v_Revised_Dremio_Path)VDS_dim_period
WHERE dlevel = 'W';

How to get rank functionality from the above sql code in qlikview code.

[sidhiq91]

@Naps_230 We do not have direct rank function in the Script. So you could follow something like below in your script. Assuming your data is already loaded in table name Main.

Table:

Load period_id,
week_ending_date,
trimester_of_year_cal,

Autonumber(week_ending_date) as Rank

Resident Main
WHERE dlevel = 'W'

Order by week_ending_date desc ;

[hic · Accepted solution]

There is a Rank() function in the Qlik engine, and it works fine in charts. Calculating the rank in the UI has the advantage that the number is then dynamic - it is calculated according your current selection.

But you seem to want to use a Rank() function in the Load script, where there is no Rank() function. The work-around is to load the rank using a combination of RecNo() and Order By.  Say that you want to rank the customers according to their Sum(SalesAmount). Then you can do this by using something like

tmp:
Load Customer, Sum(SalesAmount) as SumOfSales Resident FactTable Group By Customer;

Rank:
Load Customer, RecNo() as Rank Resident tmp Order By SumOfSales Desc;

Drop Table tmp;

But this will be a static ranking. If you want to have it dynamic, you need to calculate the rank in the UI.