Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Glenn_Renwick
Contributor III
Contributor III

Create variable for ranked position

I have the following table:

LOAD * INLINE [

Name, Value

A, 10

B, 5

C, 3

D, 4

E, 4

F, 5

G, 7

];

I would like to create a variable that returns the name of the 4th in the list when ranked.

It can't be done on load script.

Thanks for your help

1 Solution

Accepted Solutions
Glenn_Renwick
Contributor III
Contributor III
Author

I got it,

=only

        (

        if (

            Aggr(

                Rank(Sum(Value), 4, 1), [Name]) = 4,  [Name]

            )

        )

using the option 4 in the rank function was key due to the values been the same.

4 = Lowest rank on first row, then incremented by one for each row.

View solution in original post

4 Replies
anbu1984
Master III
Master III

Is Value itself rank or you want to create rank based on value?

tresesco
MVP
MVP

=FirstSortedValue(Name, Value, 4)

MayilVahanan

Hi

Try something like this

In your example, there is no 4th rank. So I changed little bit.

Load  * INLINE [
Name, Value
A, 10
B, 5
C, 3
D, 4
E, 4
F, 6
G, 7
]

= MaxString(Aggr(If(Rank(Sum(Value)) = 4,Name), Name))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Glenn_Renwick
Contributor III
Contributor III
Author

I got it,

=only

        (

        if (

            Aggr(

                Rank(Sum(Value), 4, 1), [Name]) = 4,  [Name]

            )

        )

using the option 4 in the rank function was key due to the values been the same.

4 = Lowest rank on first row, then incremented by one for each row.