Creating rankings in the load script

[#1]

I'm looking to create an item ranking based off of number sold within the load script. I want to do this, so when selecting just a certain item number in qlikview it gives that item's ranking out of all of them.

Any leads on the best way to think about this?

Thanks,

[#2]

Hi,

I would rather do it in the chart instead of the script.

It can be done in the script though I just want to make sure that you actually meant you want it done in the script.

Let's assume a Transaction table named "TXN". Also a new ranking table named RANK.

TEMP:
Load sum(Sales) as SummedSales, Customer Resident TXN Group by Customer;
RANK:
Load SummedSales, Customer, RowNo() as Rank Resident TEMP ORDER BY SummedSales DESC;

The ranking will now be in a table called RANK which you can then join to whatever other table you like. It seems simple enough, maybe I misunderstood.

Regards

Werner

[#3]

I want it done in the script so that any selections made int he report don't effect the ranking of the item based off the selected criteria..

[#4]

You can try something like the following, it works - the problem is that if you have 2 two values the exact same qlikview will rank the customer as it pleases (usually alpha), thats why the rank function in the expression works a lot better as opposed to the load script. To my nowledge rank is not available in the load script (not sure about v9 as I am working off v8.5)

Temp_Table:

LOAD customer,

sum(orders_val) as sum_val

FROM Table1

GROUP BY customer;

Table:

LOAD customer,

RowNo() as Rank,

sum_val

RESIDENT Temp_Table

ORDER BY sum_val asc; // or desc

DROP TABLE Temp_Table;

[eiconsulting]

If you get your data with aORDER BY fieldname clause the data would already in the right ranking order.

Then you should add a calculated field to accumulate the ranking. Probably using autonumber(fieldname) would do.

Flavio

[#6]

With an extra round you can solve the problems with same value ( equal to rank(expr, 0, 1)  )

Temp1_Table:

LOAD customer,

sum(orders_val) as sum_val

FROM Table1

GROUP BY customer;

Temp2_Table:

LOAD customer,

RowNo() as Rank,

sum_val

RESIDENT Temp1_Table

ORDER BY sum_val asc; // or desc

DROP TABLE Temp1_Table;

Table:

LOAD Customer,

sum_val,

if( Peek(''sum_val') = sum_val,      // compare with previous value

     Peek(''RankQualified'),            // select previous RankNo  

     Rank                                     // back to normal RankNumbers (like 1, 2, 2, 2, 5 ...)

) as RankQualified

RESIDENT Temp2_Table

ORDER BY sum_val asc; // or desc

DROP TABLE Temp2_Table;