Top 5 values

[#1]

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!

[swuehl]

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;

[#3]

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:)

[Chanty4u]

use dis exp in strt or pivot tab

lik dis

=if(Aggr(rank(sum(sales)),customer,salesperson)<=5,salesperson)

[swuehl]

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)?

[#6]

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;

  load

ProductID,

    ProviderID,

DestinationId,

    Product_Name,

    ProductCategoryID,

    Commitment_Product,

    Flag,

    CreationDate ,

    Product_Manager,

      Booking_LQ,

    Gross_LQ,

      AVG_days,

  AVG_Lead_Time,

  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:

Product_table:

LOAD  ProductID,

  ProviderID,

DestinationId,

    Product_Name,

    ProductCategoryID,

    Commitment_Product,

    Flag,

    CreationDate ,

    Product_Manager,

      Booking_LQ,

    Gross_LQ,

      AVG_days,

  AVG_Lead_Time,

AutoNumber(recno(), Product_Manager) as Rank_1

RESIDENT Product_1

WHERE Rank_1 <=5

ORDER BY Product_Manager, Gross_LQ desc;

[swuehl]

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

Product_table:

NOCONCATENATE 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;

  load

ProductID,

    ProviderID,

DestinationId,

    Product_Name,

    ProductCategoryID,

    Commitment_Product,

    Flag,

    CreationDate ,

    Product_Manager,

      Booking_LQ,

    Gross_LQ,

      AVG_days,

  AVG_Lead_Time,

  AutoNumber(recno(), Product_Manager) as Rank

  resident Product_1

  order by Product_Manager, Gross_LQ desc;

DROP TABLE Product_1;