4 Replies Latest reply: Feb 10, 2012 4:57 AM by Carlo A. Babini RSS

    Problem in order by while loading

      Hi all,

      I have a little problem in my loading script, here's the code that don't work:

       

      Sales:

      LOAD

         [...fields, including the date DATAMOV...]

      SQL SELECT *

      FROM QLIK.SALES

      ORDER BY DATEMOV ASC;

       

      LET varMinDate = Num(Peek('DATEMOV', 0, 'Sales'));

      LET varMaxDate = Num(Peek('DATEMOV', -1, 'Sales'));

      LET varToday = num(today());

       

      // Temporary calendar //

      DateField:

      Load

          $(varMinDate) + RowNo() - 1 as Num,

          date($(varMinDate) + RowNo() - 1) as TempDate

      AutoGenerate

          $(varMaxDate) - $(varMinDate) + 1;

       

       

      This code will create the temporary table DateField that I use to build a calendar.

      The problem is that varMinDate and varMaxDate are both <NULL> and the execution throws an exception, but if I remove the line "ORDER BY DATEMOV ASC", it will work fine.

      Unfortunately I cannot assume that the rows in the table "Sales" are ordered per DATEMOV in the source database.

      The table Sales could have up to 3.000.000 rows, but I think this is not the problem.

      So anyone knows a method to correctly initialize varMinDate and varMaxDate, or a possible explanation of why I get this error?

       

      Thank you in advance,

      Carlo A.