Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
prachisangewar1
New Contributor II

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

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 indicatorThreshold value 
1/1/2019a24420
2/1/2019a56420
3/1/2019a-75420
4/1/2019a12420
5/1/2019a45420
6/1/2019a-10420
7/1/2019a-5420
8/1/2019a20420
1/1/2019b634543
2/1/2019b43543
3/1/2019b454543
4/1/2019b34543
5/1/2019b463543
6/1/2019b646543
7/1/2019b43543
8/1/2019b33543
     
Labels (1)
3 Replies
anushree1
Valued Contributor II

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

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

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

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.
anushree1
Valued Contributor II

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

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)