Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

use of max(FIELD,RANK) in script with identical values

Hi,

I need to create a table in my script where i have each product, and the sum of sales of the best weeks.

I was using a syntax like :

table0:

select PRODUCT, WEEK, sum(SALES) from SALES;

myTable:

LOAD PRODUCT, numsum( max(SALES,1) , max(SALES,2) , max(SALES,3) , max(SALES,4) ) AS BEST_SALES;

Unfortunately i just noticed that max(SALES,2) return the second DISTINCT value : if for a product the sales are identical every weeks, my field BEST_SALES will be the sales of one week, and not 4 * one week.

Does anyone have an idea of a simple way to solve this problem ?

Thank you !

Tags (2)
1 Reply
thomduvi83
New Contributor III

Re: use of max(FIELD,RANK) in script with identical values

Hi,

As i was looking for the exact same thing and have found your question, i will answer on this, even if it's surely a bit too late for you...

I've found a way to achieve this using concat and subfield functions.

myMaxTable:

LOAD

     PRODUCT,

     subfield(concat(SALES,';',-SALES),';',2) as SecondBestSales (Non distinct)

RESIDENT myTable GROUP BY PRODUCT;

I hope it can help other people,

Br,

Thomas