Ranking Braches

[#1]

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

[#2]

HI Swuehl

When I Use this Script on my Original Data

During the Execution Of the following Lines

1. LOAD * WHERE Rank <= 5; 
2. LOAD 
3.   branch, 
4.   customer, 
5.   MonthYear, 
6.   TotalSales, 
7.   AutoNumber(customer, branch & MonthYear) as Rank 
8. RESIDENT TMP 
9. order by branch, MonthYear, TotalSales desc; 
10. drop table TMP; 

it is showing Error as

No Table found

Any Suggestions

[swuehl]

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.

[#4]

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;

[swuehl]

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.

[#6]

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

[#7]

HI Sunny

Thank you Very  Much For Your  Help

[sunny_talwar]

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

[swuehl]

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)