I think you would like to do something like this in the script:
1. Load sales data into Sales table
2. Load products into Products table
3. Left join onto Sales table sum of all sales
4. Left join onto Products table sum of Sales value for each product
5. Left join onto Sales table sum of Sales value for all products from step 4, for each product
6. Left join onto Products table sum of Product Sales divided by sum of all products
7. Left join onto Sales table ABC flag based on calculation from step 6
Left joins where you sum up sales values need to include a GROUP BY clause, to say what you are summing up 'by'. Syntax for some of these steps could be as follows:
3. Left join onto Sales table sum of all sales
LEFT JOIN (Sales) LOAD
SalesKey, // A unique identifier of each sales record
sum(SalesValue) AS Sales_TotalSalesValue
RESIDENT Sales;
4. Left join onto Products table sum of Sales value for each product
LEFT JOIN (Products) LOAD
ProductKey, // The key of the Product that exists on the Sales and Product tables
sum(SalesValue) AS Product_TotalSalesValue
RESIDENT Sales
GROUP BY ProductKey;
5. Left join onto Sales table sum of Sales value for all products from step 4, for each product
LEFT JOIN (Sales) LOAD
ProductKey,
Product_TotalSalesValue AS Sales_TotalProductSalesValue
RESIDENT Products;
6. Left join onto Sales table sum of Product Sales divided by sum of all products, as well as the ABC flag
LEFT JOIN (Sales) LOAD
SalesKey,
Sales_TotalProductSalesValue/Sales_TotalSalesValue AS Sales_Calc
RESIDENT Products;
7. Left join onto Sales table ABC flag based on calculation
LEFT JOIN (Sales) LOAD
SalesKey,
if(Fractile(Sales_Calc,0.8),'A',if(Fractile(Sales_Calc,0.95),'B','C')) AS Sales_CalcFilter
RESIDENT Products;
I hope this is helpful. You will have to tweak it of course, but if it doesn't suit your purposes you might want to repost your question (as seeing this many responses sometimes prevents members viewing posts)
Jonathan
