2 Replies Latest reply: Mar 3, 2011 12:16 PM by Roland Kunle RSS

    Max function needed in LOAD SCRIPT

    Henry Noh

      I have sequentially numbered days. I'd like to assign the LATEST DAY number for which there is sales data to a variable for ease of use in expression writing instead of using max(if(len(saleperson>0),daynumber))

      Is this possible? Thank you in advance!

        • Max function needed in LOAD SCRIPT
          Peter Rieper

          With the PEEK-command you can retrieve a certain record out of a table. If your table is already sorted (either descending or ascending), you may straight use

           

          LET v = PEEK('YourDateNumber', 0, 'YourTableName'); // First record
          LET LET v = PEEK('YourDateNumber', -1, 'YourTableName'); // Last record


          If the dates in your table are not sorted you need to extract into a temporary table:

           

          temp: LOAD MAX(YourDateNumber) AS LastDate RESIDENT YourTableName;
          LET v = PEEK('YourDateNumber', 0, 'YourTableName');
          DROP TABLE temp:


          Please note the apostrophs in the PEEK-command.

          HTH
          Peter

          • AW:Max function needed in LOAD SCRIPT

            Hello henry,

            check out this script code:

             

            TT:
            load * Inline [
            Id, Val, salesperson
            1, 10, a
            2, 20, a
            3, 30, b
            4, 40, c
            5, 50, b
            ];

            load
            salesperson,
            max(if(len(salesperson>0),Val)) as MaxVal
            Resident TT
            Group by salesperson;


            Regards, Roland