topic Re: Top 5 values in QlikView

Top 5 values

Thu, 10 Dec 2015 12:26:34 GMT

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!

Re: Top 5 values

swuehl — Thu, 10 Dec 2015 23:06:43 GMT

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;

Re: Top 5 values

Tue, 15 Dec 2015 09:26:09 GMT

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

Re: Top 5 values

Chanty4u — Tue, 15 Dec 2015 10:08:51 GMT

use dis exp in strt or pivot tab

lik dis

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

Re: Top 5 values

swuehl — Tue, 15 Dec 2015 10:09:06 GMT

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

Re: Top 5 values

Wed, 16 Dec 2015 13:27:23 GMT

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;

Re: Top 5 values

swuehl — Wed, 16 Dec 2015 19:56:28 GMT

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;