topic Re: Creating rankings in the load script in QlikView

Creating rankings in the load script

Thu, 03 Sep 2009 20:38:40 GMT

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,

Creating rankings in the load script

Thu, 03 Sep 2009 21:26:04 GMT

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

Creating rankings in the load script

Thu, 03 Sep 2009 21:49:21 GMT

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..

Creating rankings in the load script

Thu, 03 Sep 2009 22:29:58 GMT

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;

Creating rankings in the load script

eiconsulting — Thu, 03 Sep 2009 22:48:43 GMT

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

Re: Creating rankings in the load script

Tue, 02 Oct 2012 11:04:03 GMT

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;