ranking in load script

[gandalfgray]

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

[MayilVahanan]

HI

PFA

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

[swuehl · Accepted solution]

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;

[#4]

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]

@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]

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]

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]

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]

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]

Thanks!