topic Using Rank to get nth value in QlikView

Using Rank to get nth value

pauluk__ — Tue, 22 May 2018 15:34:32 GMT

Hi all, I was wondering if you could help me with a query about getting ranked value n from a list.

Let's say I have a table of KPI_IDs which are either available in the selections or not. I would like to be able to get, for example, the KPI_ID value of the 3rd ranked selected value, which according to the below would be 5:

KPI_ID	Selected	Ranked Value
1
2	YES	1
3
4	YES	2
5	YES	3
6
7	YES	4

Do you know how I would be able to do this with the expression.

Thank you.

Re: Using Rank to get nth value

olivierrobin — Tue, 22 May 2018 15:37:42 GMT

hello

did you try to use a set analysis in your rank function ?

Selected={'YES'}

Re: Using Rank to get nth value

pauluk__ — Tue, 22 May 2018 15:41:39 GMT

Hi Olivier,

This is more really to do with how I could get the value in the first column, as Rank on its own would return the third value. Outside a table, I would like to be able to know the KPI_ID value of, say, the third ranked value.

Re: Using Rank to get nth value

olivierrobin — Tue, 22 May 2018 15:47:13 GMT

well

i would do that in script. with a load and rank() by excluding Selected<>'YES'

basically, something

t1:

load kpi,sum(value) as v group by kpi where selected='YES'

t2:

load kpi,rowno() resident t1 order by v descending

Re: Using Rank to get nth value

isingh30 — Tue, 22 May 2018 15:49:49 GMT

You need this -

Thank You!

Re: Using Rank to get nth value

pauluk__ — Tue, 22 May 2018 16:01:50 GMT

Hi Ishtdeep,

Really I'm looking for an expression, outside a table that would answer the question 'Which is the third ranked KPI_ID value' and return 5 in the above example.

Re: Using Rank to get nth value

isingh30 — Tue, 22 May 2018 16:08:32 GMT

only({<Selected = {'YES'}>}[Ranked Value])