Rank Function load script

[#1]

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

[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:

[#3]

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]

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

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

[sunny_talwar · Accepted solution]

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]

Here is the latest version based on your requirement:

And the data model, just another way:

[#7]

Thank youu It works perfectly! You just made my day

[#8]

Thank youu

[hugmarcel]

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