topic Re: ranking in load script in QlikView

ranking in load script

gandalfgray — Tue, 20 Nov 2012 16:17:21 GMT

I want to rank my data in descending order so that two or more records that have the same ranking value gets the same rank.

Consider the following indata:

before:

LOAD * INLINE [

F1, F2

A, 14

B, 22

C, 9

D, 9

E, 5

F, 3

G, 5

H, 9

];

I want the result shown below (note the jump in rank when there was several with the same rank "before")

ranked_data result table:

F1, F2, rank

A, 14, 2

B, 22, 1

C, 9, 3

D, 9, 3

E, 5, 6

F, 3, 8

G, 5, 6

H, 9, 3

Any clever solution for this would be appreciated!

/gg

Re: ranking in load script

MayilVahanan — Tue, 20 Nov 2012 16:59:18 GMT

PFA

No rank function in script, but you can try like this

Re: ranking in load script

swuehl — Tue, 20 Nov 2012 17:01:50 GMT

Maybe just like

INPUT:

LOAD * INLINE [

F1, F2

A, 14

B, 22

C, 9

D, 9

E, 5

F, 3

G, 5

H, 9

];

RANK:

LOAD *,

if(F2 = peek(F2), peek(Rank), recno()) as Rank

Resident INPUT order by F2 desc;

drop table INPUT;

Re: ranking in load script

Tue, 20 Nov 2012 17:09:21 GMT

Give this a try.

Data:
LOAD * INLINE [
    F1, F2
    A, 14
    B, 22
    C, 9
    D, 9
    E, 5
    F, 3
    G, 5
    H, 9
];

Ranked_Data:
LOAD
*,
RowNo() AS Rank_tmp
Resident Data
ORDER BY F2 DESC;

INNER JOIN
LOAD
F2,
MIN(Rank_tmp) AS Rank

Resident Ranked_Data
GROUP By F2;

Drop Table Data;

Rank2 is the one that you would end up using.

-John

Re: ranking in load script

gandalfgray — Wed, 21 Nov 2012 07:29:59 GMT

@swuehl:

Elegant!

Works as a charm.

0sec to run for 15000 records.

@john:

Also works as it should!

@Mayil:

Works in the chart, but I was specifically looking for a script solution since I want to store the result as a qvd.

Thanks all for the effort!

/gg

Re: ranking in load script

yanivbm88 — Tue, 01 Aug 2017 12:18:41 GMT

Hi Stefan,

How would you make it a conditional rank?

For example, ranking only lines where F2>=9, so the output will be:

F1, F2, rank

A, 14, 2

B, 22, 1

C, 9, 3

D, 9, 3

E, 5, null()

F, 3, null()

G, 5, null()

H, 9, 3

Re: ranking in load script

swuehl — Tue, 01 Aug 2017 12:26:48 GMT

Maybe change the above code to something like

RANK:

LOAD *,

if(F2 = peek(F2), peek(Rank), If(F2>9, recno() )) as Rank

Resident INPUT order by F2 desc;

Re: ranking in load script

yanivbm88 — Tue, 01 Aug 2017 12:45:53 GMT

Thanks for your response,

I chekced your suggestion. It Is indeed defining the lines that does not comply with the condition as null, but the ranking still considers them.

In a different case I will be able to provide an example:

For example, ranking only lines where F2<9, so the output will be:

F1, F2, rank

A, 14, null()

B, 22, null()

C, 9, null()

D, 9, null()

E, 5, 1

F, 3, 3

G, 5, 1

H, 9, null()

With your suggestion, I get:

F1, F2, rank

A, 14, null()

B, 22, null()

C, 9, null()

D, 9, null()

E, 5, 6

F, 3, 8

G, 5, 6

H, 9, null()

Re: ranking in load script

swuehl — Tue, 01 Aug 2017 14:51:46 GMT

You were specifically asking for a filter F2>=9 and it should work as suggested with this filter.

If you need to filter F2<9 or if you want to use another approach anyway, you can try with the Autonumber() function to create the rank:

INPUT:

LOAD * INLINE [

F1, F2

A, 14

B, 22

C, 9

D, 9

E, 5

F, 3

G, 5

H, 9

];

RANK:

LOAD *,

If(F2<9,Autonumber(F2,'Rank')) as Rank

Resident INPUT order by F2 desc;

drop table INPUT;

Re: ranking in load script

yanivbm88 — Thu, 03 Aug 2017 12:51:53 GMT

Thanks!