1 Reply Latest reply: Jun 9, 2011 7:29 AM by Erich Shiino RSS

    Problems with calendar after concatenation

      Hi all,

       

      I am a brand new user of QlikView.

      I have two tables with a date field and created a master calendar.

      When I then load the script, I do not encounter any problems.

      But when I concatenate the two tables, I have a problem with my varMaxDate which returns NULL.

      I checked the dates in the two tables and there are no NULL values in the date fields ...

      Any suggestions to resolve this ??

       

      Davy

       

      Script:

       

      Sales:

      LOAD

           ...,

           F1FADT as Date,

           ...;

      FROM [Database.Table1]

      ORDER BY F1FADT ASC;

       

      Concatenate(Sales)

      LOAD

           ...,

           KACRED as Date,

           ...;

      FROM [Database.Table2]

      ORDER BY KACRED ASC;

       

      let varMinDate = num(peek('Date', 0, 'Sales'));

      let varMaxDate = num(peek('Date', -1, 'Sales'));

      let varToday = num(today());

      let var0 = date(0);

       

      TempCalendar:

      LOAD

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

           $(varMinDate) + rowno() -1 as Tempdate

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

        • Re: Problems with calendar after concatenation
          Erich Shiino

          I would change your script a little bit. I think the peek('Date',-1, etc... would not get your max date...

           

           

           

           

          Sales:
          LOAD
               ...,
               F1FADT as Date,
               ...;
          FROM [Database.Table1]
          ORDER BY F1FADT ASC;
          
          Concatenate(Sales)
          LOAD
               ...,
               KACRED as Date,
               ...;
          FROM [Database.Table2]
          ORDER BY KACRED ASC;
          
          maxdate:
          load max(Date) as maxDate resident Sales;
          let varMaxDate = num(peek('maxDate'));
          drop table maxdate;
          
          mindate:
          load min(Date) as minDate resident Sales;
          let varMinDate = num(peek('minDate'));
          drop table mindate;
          
          let varToday = num(today());
          let var0 = date(0);
          
          TempCalendar:
          LOAD
               $(varMinDate) + rowno() -1 as Num,
               $(varMinDate) + rowno() -1 as Tempdate
          AUTOGENERATE $(varMaxDate) - $(varMinDate)+1;
          

           

          Hope this helps,

           

           

          Erich