16 Replies Latest reply: Jan 21, 2014 3:50 AM by Nicolas MARTIN RSS

    My "ORDER BY" on a resident table doesn't work

    Nicolas MARTIN

      Hello,

       

      I need to get the value before the last in a specific field, in QlikView script.

       

      Here is the code I use:

       

      Data:

      LOAD * INLINE [

      YearMonth

      201312

      201312

      201306

      201307

      201308

      201309

      201310

      201311

      201311

      ];

       

       

       

      OrderBy:

      LOAD

      DISTINCT YearMonth AS TMP_date

      RESIDENT Data

      ORDER BY YearMonth DESC;

       

       

       

      LET vValue = peek('TMP_date', 1, 'OrderBy'); // 1 = peek the 2nd value.

       

       

       

      This script returns:

      2014-01-07_165650.png

       

      and my variable is "201306", whereas it should be "201311".

      --> the ORDER BY don't make sense.

       

       

      Could you explain me why?

       

       

       

       

      I use the following workaround:

      OrderBy:

      LOAD

      DISTINCT YearMonth AS TMP_date

      RESIDENT Data

      ORDER BY YearMonth ASC;

       

       

      OrderBy2:

      LOAD

      DISTINCT TMP_date AS TMP_date2

      RESIDENT OrderBy

      ORDER BY TMP_date DESC;

       

       

      LET vValue = peek('TMP_date2', 1, 'OrderBy2');

      It works, but it's not "clean".