Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
gandalfgray
Specialist II
Specialist II

ranking in load script

Hi

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

9 Replies
MayilVahanan

HI

PFA

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
swuehl
MVP
MVP

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;

Not applicable

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

gandalfgray
Specialist II
Specialist II
Author

@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

yanivbm88
Creator
Creator

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

swuehl
MVP
MVP

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;

yanivbm88
Creator
Creator

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()

swuehl
MVP
MVP

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;

yanivbm88
Creator
Creator

Thanks!