Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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..
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;
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
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;