Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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