Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank Function load script

Hi,

I want to establish some values according to the rank of the data on my field in my load script.

My table is like this:

Name   Value

A             4.4

B             0.9

C             3.2

D             1.92

I want it to rank my values so the output will be like this:

Name     Rank

A            4

B            1

C            3

D            2

And finally I want to translate each rank value into a description, if it is less or equal to 2, then "Z", if not, "Y"

Name     Result

A            Y

B            Y

C           Z

D          Z

I don't know why I am wrong, but I can't get the statements right on the load script

Thank you

1 Solution

Accepted Solutions
sunny_talwar

Just add desc to order by statement:

Table:

LOAD * Inline [

NAME, VALUE

A, 4.4

B, 0.9

C, 3.2

D, 1.92

];

Table1:

LOAD *,

  If(Rank <= 2, 'Z', 'Y') as Result;

LOAD NAME,

  VALUE,

  AutoNumber(VALUE) as Rank

Resident Table

Order By VALUE desc;

DROP Table Table;

View solution in original post

8 Replies
sunny_talwar

There is no direct rank function in script, but you can use AutoNumber to get you what you want:

Table:

LOAD * Inline [

NAME, VALUE

A, 4.4

B, 0.9

C, 3.2

D, 1.92

];

Table1:

LOAD *,

  If(Rank <= 2, 'Z', 'Y') as Result;

LOAD NAME,

  VALUE,

  AutoNumber(VALUE) as Rank

Resident Table

Order By VALUE;

DROP Table Table;


Output:

Capture.PNG

Not applicable
Author

Thank you very much

Although I just noticed that I wrote the rank in the other way around, I mean, instead of:

Value Rank  ----->  Value   Rank

4.4      4                4.4         1

0.9      1                0.9          4    

3.2      3                3.2          2

1.92     2               1.92        3

Do you have any clue of how can I change it?

Thanks so much for your time

sinanozdemir
Specialist III
Specialist III

Here is another way by using Previous and Peek functions in an If statement:

Capture.PNG

As a result of it, your data model will look like the below:

Capture.PNG

sunny_talwar

Just add desc to order by statement:

Table:

LOAD * Inline [

NAME, VALUE

A, 4.4

B, 0.9

C, 3.2

D, 1.92

];

Table1:

LOAD *,

  If(Rank <= 2, 'Z', 'Y') as Result;

LOAD NAME,

  VALUE,

  AutoNumber(VALUE) as Rank

Resident Table

Order By VALUE desc;

DROP Table Table;

sinanozdemir
Specialist III
Specialist III

Here is the latest version based on your requirement:

Capture.PNG

And the data model, just another way:

Capture.PNG

Not applicable
Author

Thank youu It works perfectly! You just made my day

Not applicable
Author

Thank youu

hugmarcel
Specialist
Specialist

Hi

see my example file rank.qvw.

- it contains a sub providing flexible ranking mechanisms,
  such as ranking by autonumber or by rowno().
- ranks can be split into half ranks for identical values
  (e.g. rank 11,12,13 -> 12, 12, 12)

https://community.qlik.com/docs/DOC-13949


Regards - Marcel