topic To get the nth ranked value for a dimension at script level in QlikView

To get the nth ranked value for a dimension at script level

prachisangewar1 — Sat, 16 Nov 2024 03:30:12 GMT

Hi,

I have a requirement where i need to get the nth ranked value for a dimension, sample table given below

The Column Threshold value is what I want to compute.

So we are looking at a range of dates for the customers and the indicator is computed on some logic.

The Threshold value is based on the value in the indicator column. It is the nth rank value for each customer where n is the value in the indicator column.

I have tried to use Firstsortedvalue and Max , but both need a number or a variable in the rank agreement. I want to use the indicator column.

Formula i used :

FirstSortedValue(Value,-Value,indicator) - gives wrong result

Max(Value,indicator) - gives wrong result

I cannot give a fixed number in the last agreement of both the functions as it changes with customer. Please advise if any solution for this scenario.

Date	Customer	Value	indicator	Threshold value
1/1/2019	a	24	4	20
2/1/2019	a	56	4	20
3/1/2019	a	-75	4	20
4/1/2019	a	12	4	20
5/1/2019	a	45	4	20
6/1/2019	a	-10	4	20
7/1/2019	a	-5	4	20
8/1/2019	a	20	4	20
1/1/2019	b	634	5	43
2/1/2019	b	43	5	43
3/1/2019	b	454	5	43
4/1/2019	b	34	5	43
5/1/2019	b	463	5	43
6/1/2019	b	646	5	43
7/1/2019	b	43	5	43
8/1/2019	b	33	5	43

Re: To get the nth ranked value for a dimension at script level

anushree1 — Thu, 23 May 2019 06:48:21 GMT

Could you please state the input and the output in this scenario as the explanation seems a little confusing

Re: To get the nth ranked value for a dimension at script level

prachisangewar1 — Thu, 23 May 2019 08:05:45 GMT

Hi,

Date, Customer, Value and indicator are input.
The out put required is Threshold value.
I have to lookup on the customer and get the nth highest value in the "Value" column, where n is the value in indicator column.
Let me know if any other clarifications required.

Re: To get the nth ranked value for a dimension at script level

anushree1 — Thu, 23 May 2019 13:17:20 GMT

Please use the below script:

Table:

LOAD
"Date ",
"Customer",
"Value",

indicator
FROM [lib://test apps/nth threshold.xlsx]
(ooxml, embedded labels, table is Sheet1)
;

Final:

Load * ,
AutoNumber(RowNo(),"Customer") as Seq
Resident Table
Order by "Customer",Value desc ;
drop table Table;
Left Join
Test:
Load "Customer",
"Value" as Threshold
resident Final
Where Seq=indicator;

In the front end create a Straight Table with Dimension Date, Customer and Expression Only(Threshold)