topic Re: Ranking Braches in QlikView

Ranking Braches

Fri, 19 Jun 2015 14:16:30 GMT

Hi Experts

I am having a Sales table with multiple branches,Customers and their sales

now I want to rank the customers for each Month by branch according to sales and take top 5 customers

from each branch to a chart

I want the ranking to done on the Script itself

please help me in doing this

Re: Ranking Braches

sunny_talwar — Fri, 19 Jun 2015 14:19:02 GMT

Rank the total sales (for all dates) or each date's rank?

Re: Ranking Braches

Fri, 19 Jun 2015 14:23:39 GMT

HI Sunny

Sorry Rank by Month

Re: Ranking Braches

sunny_talwar — Fri, 19 Jun 2015 14:30:26 GMT

Try the following script:

Table:

LOAD branch,

date,

MonthName(date) as MonthYear,

customer,

sales

FROM

Community_169202.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(Table)

LOAD branch,

customer,

MonthYear,

Sum(sales) as TotalSales

Resident Table

Group By branch, customer, MonthYear;

Join(Table)

LOAD distinct branch,

MonthYear,

customer,

TotalSales,

If(branch = Peek('branch'), Alt(Peek('Rank'), 1) + 1, 1) as Rank

Resident Table

Order By branch, MonthYear, TotalSales desc;

Best,

Sunny

Re: Ranking Braches

Fri, 19 Jun 2015 22:36:22 GMT

Hi Sunny

When I use this script in my actual data, My System Got hanged twice

Is there any other option for this

please Suggest

Re: Ranking Braches

sunny_talwar — Sat, 20 Jun 2015 02:15:13 GMT

How big is your database?

Re: Ranking Braches

Sat, 20 Jun 2015 08:47:08 GMT

Hi Sunny

22 million transaction Lines

there are 152 branches

Re: Ranking Braches

swuehl — Sat, 20 Jun 2015 09:59:47 GMT

As I've understood, you wan to rank sales by customer per MonthYear and branch.

I think above solution only does it per branch.

Joining the complete fact table back to itself is somewhat ressource expensive (and the distinct load prefix will be performed on the output table!).

Maybe try something like this:

// First create more random data, since your excel only shows data for a #1 customer rank per MonthYear and branch.
// You don't need the foor loop and the customer / sales mods, just load your real data from your source.

FOR i = 1 to 20

Table:
LOAD branch,
    date,
    MonthName(date) as MonthYear,
    customer + floor(RAND()*10) as customer,
    sales + ceil(RAND()*100) as sales
FROM
Community_169202.xlsx
(ooxml, embedded labels, table is Sheet1);

NEXT i

// sum(sales) grouped by customer, branch, MonthYear
TMP:
LOAD branch,
  customer,
  MonthYear,
  Sum(sales) as TotalSales
  //Rank(Sum(sales)) as Rank2
Resident Table
Group By branch, customer, MonthYear;

//Create the rank and Join back to fact table. You can comment out the preceding load to get a full ranking

LEFT JOIN (Table)
LOAD * WHERE Rank <= 5;
LOAD
  branch,
  customer,
  MonthYear,
  TotalSales,
  AutoNumber(customer, branch & MonthYear) as Rank
RESIDENT TMP
order by branch, MonthYear, TotalSales desc;


drop table TMP;

Re: Ranking Braches

Digvijay_Singh — Sat, 20 Jun 2015 12:37:16 GMT

Dear Swuehl,

I am learning a lot from your responses(Thanks!).

Just couldn't get what portion is giving rank 1 to highest sale group by monthyear and then rest of the ranks in descending order of TotalSales?

Thanks,

Digvijay

Re: Ranking Braches

swuehl — Sat, 20 Jun 2015 12:42:17 GMT

In the last load statement, the records will come in ordered by branch, MonthYear, TotalSales descending, so you just need to start counting customers from 1 whenever there is change in the combination branch and MonthYear.

This is done by Autonumber() function, you can do the same with a peek() logic as sunindia demonstrates above.

edit: For me, it's a little easier to read and understand what the autonumber is doing compared to the method using peek().

But I've seen perfomance issues using Autonumber() when there is a large number of combinations of branch / MonthYear.

Re: Ranking Braches

Sun, 21 Jun 2015 18:15:31 GMT

HI Swuehl

When I Use this Script on my Original Data

During the Execution Of the following Lines

LOAD * WHERE Rank <= 5;
LOAD
branch,
customer,
MonthYear,
TotalSales,
AutoNumber(customer, branch & MonthYear) as Rank
RESIDENT TMP
order by branch, MonthYear, TotalSales desc;
drop table TMP;

it is showing Error as

No Table found

Any Suggestions

Re: Ranking Braches

swuehl — Sun, 21 Jun 2015 18:25:10 GMT

Could you post your complete script or document log?

Seems like table TMP is not found, but I can't see the reason for that only seeing the snippet you've posted.

Re: Ranking Braches

Sun, 21 Jun 2015 18:41:27 GMT

HI Swuehl

This is the actual Script I am Using

SALES:

LOAD BRNUM,

MonthName (DATE) AS DEC_Month,

CUSTNUM + floor(RAND()*10) as CUSTNUM ,

SALES+ ceil(RAND()*100) as SALES

FROM

QVD\SALES2015.qvd

(qvd);

TMP:

LOAD BRNUM,

DEC_Month,

CUSTNUM,

Sum(SALES) as SALES

Resident SALES

Group By

BRNUM,

DEC_Month,

CUSTNUM;

LEFT JOIN (SALES)

LOAD * WHERE Rank <= 200;

LOAD BRNUM,

DEC_Month,

CUSTNUM,

SALES,

AutoNumber(CUSTNUM, BRNUM& DEC_Month) as Rank

RESIDENT TMP

order by BRNUM, DEC_Month, SALES desc;

Re: Ranking Braches

swuehl — Sun, 21 Jun 2015 18:48:50 GMT

In your TMP table, you used an alias SALES instead of TotalSales for the aggregated Sales per customer.

Thus (having same number and name of fields) the TMP table will be auto-concatenated to the SALES table and will not exist as a separate table in the data model.

I suggest you start with using the same field names as in my example.

You also don't need to modify your customer and sales data when reading from your source, I did this only because your sample data set showed not enough data for your ranking request.

CUSTNUM + floor(RAND()*10) as CUSTNUM ,

SALES+ ceil(RAND()*100) as SALES

should be replaced with

CUSTNUM,

SALES

when handling your real data.

Re: Ranking Braches

Sun, 21 Jun 2015 21:14:11 GMT

Hi Swuehl

Thanks as Lot,

Thank you Very For your Help

One More Question

Now I Want to Take sales for top 200 Customers for each branch for current month - sales for top 200 Customers for each branch from Last Month

How Can I do This Please Suggest

Re: Ranking Braches

Sun, 21 Jun 2015 21:19:11 GMT

HI Sunny

Thank you Very Much For Your Help

Re: Ranking Braches

sunny_talwar — Sun, 21 Jun 2015 21:58:56 GMT

You are very welcome. I wish I could have been more helpful, but I am glad you got the help from no less than the best swuehl

Best,

Sunny

Re: Ranking Braches

swuehl — Sun, 21 Jun 2015 22:06:45 GMT

Here is a sample QVW that demonstrates how you can compare the monthly sales for top customers in a chart (top 5 customers in my sample, but easy to adapt to 200)