Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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;

View solution in original post

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