topic Re: Rank Function load script in QlikView

Rank Function load script

Fri, 26 Jun 2015 20:48:23 GMT

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

Re: Rank Function load script

sunny_talwar — Fri, 26 Jun 2015 20:53:11 GMT

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:

Re: Rank Function load script

Fri, 26 Jun 2015 21:03:15 GMT

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

Re: Rank Function load script

sinanozdemir — Fri, 26 Jun 2015 21:03:40 GMT

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:

Re: Rank Function load script

sunny_talwar — Fri, 26 Jun 2015 21:05:30 GMT

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;

Re: Rank Function load script

sinanozdemir — Fri, 26 Jun 2015 21:08:06 GMT

Here is the latest version based on your requirement:

And the data model, just another way:

Re: Rank Function load script

Fri, 26 Jun 2015 21:08:11 GMT

Thank youu It works perfectly! You just made my day

Re: Rank Function load script

Fri, 26 Jun 2015 21:09:17 GMT

Thank youu

Re: Rank Function load script

hugmarcel — Sun, 15 Nov 2015 11:16:37 GMT

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