Using WRank() to archieve Over partition by

[zhaofeng]

Hi Qlikers

As a brand new function WRank() has been added to Qlik Sense,I found that we can use this function to archieve over partition by 

Here is my script

Load
	*,
    Window(WRank(0,1),Dim1,'desc',Measure) as RANK
;
Load * Inline [
Dim1,Dim2,Measure
A,A1,1
A,A2,6
A,A3,4
B,B1,9
B,B2,20
B,B3,16
B,B4,8
]
;

After you reload the script,you can get a new fields in the table.

Sincerly appreciated if you have any better idea about this.

Reagrds!

[MayankDaga]

So basically WRank simply ranks based on the dimension you have passed in the function. For eg. in your case, for Dim1=A, you have 1,6 and 4 as the measure.

Within this, 6 is the highest, then 4 and then 1. It is ranked accordingly, so the record against Dim1=A and measure=6 is ranked 1 and so on..

[Kushal_Chawda]

@zhaofeng  Qlik window function is really good addition if we want to create partition by fields in script. While I haven't used it much, there is also performance consideration. But I always try to use chart expression if it is easily achievable through it to avoid any script reload performance issues. In this case you can easily achieve rank using below chart expression

=aggr(rank(sum(Measure)),Dim1,Dim2) 

Having said that always check the performance impact before using windows functions in the script. 

[MayankDaga]

Hi @Kushal_Chawda 

I think this is a really helpful script function if you want to calculate Ranks as static values (read dimension) that shouldn't change based on selections.

For eg. when your application has section access applied, doing it on the expression side will show an incorrect rank as data itself is restricted.

Only way to do this earlier was either get in done in the DB using Rank/Dense Rank functions or either using some complex Peek/Previous/Autonumber/RowNo() logics.

Regards,

M

[Kushal_Chawda]

@MayankDaga  I understand that but my point was to consider performance impact before using it. It really depends on business needs when you want to choose the approach. Depending on the requirements, we need to decide the best approach considering performance impact.