Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have many product managers, each PM has many productIDs and product_Gross.
I would like to calculate in the scripts only top 5 products for every product manager, by gross amount.
thanks!
You can try something like
INPUT:
LOAD PM, productID, product_Gross
FROM YourSource;
RESULT:
LOAD *
WHERE Rank <=5;
LOAD PM, productID, product_Gross, AutoNumber(recno(), PM) as Rank
RESIDENT INPUT
ORDER BY PM, product_Gross desc;
DROP TABLE INPUT;
Hi! it's not working... is there an other way to do this in the script? if not, any way of doing it in an expression in a straight/pivot table?
thank you:)
use dis exp in strt or pivot tab
lik dis
=if(Aggr(rank(sum(sales)),customer,salesperson)<=5,salesperson)
What do you mean with 'not working'?
Maybe we just need a minor correction of the code. Could you upload some sample lines of data (e.g. in an Excel file)?
I tried in 2 different ways, one was exactly like you suggested, the other one with minor changes.
i have more than 3 fields in the table, i marked the 3 important fields below:
1) the solution you suggested, no error msg, but the table Product_table was not loaded.
Product_table:
load
ProductID,
ProviderID,
DestinationId,
Product_Name,
ProductCategoryID,
Commitment_Product,
Flag,
CreationDate ,
Product_Manager,
Booking_LQ,
Gross_LQ,
AVG_days,
AVG_Lead_Time,
where Rank<=5;
AutoNumber(recno(), Product_Manager) as Rank
resident Product_1
order by Product_Manager, Gross_LQ;
2)your solution with minor adjustment. i got error msg Rank_1 field was not found:
LOAD ProductID,
AutoNumber(recno(), Product_Manager) as Rank_1
RESIDENT Product_1
WHERE Rank_1 <=5
ORDER BY Product_Manager, Gross_LQ desc;
Second solution won't work because of the WHERE clause calling Rank_1 field that is only existing in the output table.
First solution, I assume that your Product_table shows the same field names as Product_1 table, so it will be auto-concatenated.
Try
NOCONCATENATE LOAD
order by Product_Manager, Gross_LQ desc;
DROP TABLE Product_1;
