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

[#1]

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 !

[thomduvi83]

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