Dynamic rank on FirstSortedValue() in load script

[rmainhart]

(Edit: removed link to another community post due to being marked as spam)

I need to find the Nth value within each group from a resident table.  For example, from Group A, I may need the 2nd value, but from Group B, I may need the 5th value.  One table (or a mapping table) includes the name of each group, and the value for N.  A second table includes the name of the group, an ID number, and the value.

[Map-NthValue]:
GroupName|NthValue
A|2
B|5
C|3
...

[Fact table]:
GroupName|ID|Value
A | 111 | 98
A | 222 | 97
A | 333 | 90
B | 123 | 99
B | 222 | 99
B | 333 | 88
B | 456 | 87
B | 789 | 86
B | 888 | 85
B | 999 | 80
C | 123 | 99
C | 234 | 99
C | 345 | 98
C | 456 | 98
C | 567 | 70
...

Expected output:

A | 97  // 2nd sorted value

B | 86  //5th sorted value

C | 98 // 3rd sorted value

 Code I've tried:

Load
    GroupName
    ,FirstSortedValue(distinct Value, -Value, ApplyMap('Map-NthValue', GroupName, 1)) as NthValue
Resident [Fact table]
Group By GroupName
;

This doesn't work.

I also tried wrapping the <rank> parameter of the FirstSortedValue() function inside a dollar-sign expansion:

  ,FirstSortedValue(distinct Value, -Value, $(=ApplyMap('Map-NthValue', GroupName, 1)) ) as NthValue

 This returns unexpected values (usually the 1st sorted value within each grouping, I think).

Wrapping the ApplyMap() in an Only() function was also tried.

I need to do this in the load script, because there are further manipulations of the data after this particular step.

I feel like I am very close with the syntax, but just need a bit of extra help.  Thank you!

[SerhanKaraer]

Hello Rmainhart,

You could use Autonumber for partitioning records and then apply your filters on records number assuming your records are sorted beforehand.

LOAD GroupName, ID, Value WHERE PartitionedRowNo = ApplyMap('Map-NthValue',GroupName);
LOAD GroupName, ID, Value, AutoNumber(RecNo(), GroupName) as PartitionedRowNo;

[rmainhart]

Thank you for the suggestion.  I did use a similar solution as a workaround ... but according to documentation, the Rank parameter of FirstSortedValue() should accept an expression.  How to construct the syntax so Rank parameter is an expression and not a hardcoded value?

[SerhanKaraer]

Hi Rmainhart,

Documentation states rank as: By stating a rank "n" larger than 1, you get the nth sorted value.

I guess it implies an expression which finally evaluates as a scalar value for all records. There are 3 functions (min, max, firstsortedvalue) in BNF which has a ranking parameter. Similarly, the other functions also require a scalar parameter as rank.

[marcus_sommer]

I think it's an error within the documentation and that the script-versions of min/max/firstsortedvalue doesn't support the ranking-feature else only the UI versions.

This means you need a different solutions. This may be such counting with the autonumber() but in my experience it's rather slow by larger data-sets and so I suggest to implement it with interrecord-functions like peek() and previous().

- Marcus 

[rmainhart]

The script version of FirstSortedValue does accept hardcoded values for the rank parameter.  (Specifying a rank of 15 does return the 15th sorted value.)  The issue seems to be with using an expression that is something other than a hardcoded value.

To your point and @SerhanKaraer 's , however, yes I did end up implementing a completely different architecture in order to replicate the functionality of the FirstSortedValue function.  I built a bridge table, sorted by GroupName ascending and Score descending, and used RowNo() to generate an index.  Next, a mapping table, containing just the Index and Score.  Using Min(Index) Group By GroupName gives the row number of the first record in each group.  To this, add the value of NthValue, minus one, and we have the index number of the score we need, which we then retrieve via ApplyMap(). 

[marcus_sommer]

Yes, a fixed value worked. Also an expression like 1 + 2 but no expression which would return different results depending on the grouped records. I assume that's caused from the way how the aggregation is performed which is probably a two-level measurement. Means the aggregation against the entire table respectively the specified grouping and then picking the fixed-value record. But to return a variable n-th result a third level would be needed which did the appropriate comparing and then picking the specified ones.

Therefore I don't think that there is anything wrong with the syntax or that there is a bug else that this multi-level aggregation feature isn't implemented within the script. I could imagine that this feature may have exists within the first major-releases and that Qlik has removed it because of the performance and/or for other changes within the calculation routine. In each way the documentation isn't quite correct and should be adjusted.

- Marcus