1 Reply Latest reply: Nov 29, 2013 3:43 AM by Thomas Duvivier RSS

    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 !

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

          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