4 Replies Latest reply: Mar 20, 2015 9:18 AM by Rayna Curtis RSS

    Scripting with a Master Calendar

    Rayna Curtis

      I have a situation where my table rows have a "valid from date" and a "valid to date".  I need to be able to show all individual dates in the next x years, and what rows of the table are valid for each date.  So, if the row has a valid from date of May 1st, 2011 and a valid to date of October 31st, 2011, I need to be able to see each of the 180-or-so dates, one by one, and see that this particular row is valid for that particular day.  (The YldSuffix is the row identifier.)  There can be many valid rows for each individual date, and many individual dates for each valid row, in case that makes any difference.

       

      To get all the individual dates, I created a Master Calendar, but I don't appear to be able to reference it in a table load so that I can do a comparison.  Here is the script I wrote:

      --------------------

      Temp:

      Load

                     min(YldFromDate) as minDate,

                    max(YldToDate) as maxDate

      Resident Yield;

       

      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

      DROP Table Temp;

       

      TempCalendar:

      LOAD

                     $(varMinDate) + IterNo()-1 As Num,

                     Date($(varMinDate) + IterNo() - 1) as TempDate

                     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

       

      MasterCalendar:

      Load

                     TempDate AS "RoomNightValidDate",

                     weekend(TempDate) As "RoomNightValidWE",

                     Year(TempDate) As "RoomNightValidYear",

                     Month(TempDate) As "RoomNightValidMonth",

                     WeekDay(TempDate) as "RoomNightValidDOW"

      Resident TempCalendar

      Order By TempDate ASC;

      Drop Table TempCalendar;

       

      validYld:

      load

             "suffix_no" as "YldSuffix",

             "to_date",

             "from_date" ,       

             TempDate AS "RoomNightValidDate"

             where b.TempDate >= a.to_date and b.TempDate <= a.from_date

           ;

      SQL SELECT *

      FROM fdgbrtover a, MasterCalendar b;

      --------------------


      The script errors out and keeps telling me that the MasterCalendar is an invalid object.  I find it hard to believe that you can create a calendar but not use it for any comparative purpose.  Am I wrong?

       

      I suspect that I am just messing up the syntax, and any assistance would by most appreciated.

        • Re: Scripting with a Master Calendar
          Mohammed Mukram

          Dear Rayna,

           

          The error is in below script.

           

          validYld:

          load

                 "suffix_no" as "YldSuffix",

                 "to_date",

                 "from_date" ,       

                 TempDate AS "RoomNightValidDate"

                where b.TempDate >= a.to_date and b.TempDate <= a.from_date

               ;

          SQL SELECT *

          FROM fdgbrtover a, MasterCalendar b;


          could you please share the sample file. so we can help you in better way.


          Thanks,

          Mukram.


          • Re: Scripting with a Master Calendar
            Bill Markham

            Maybe move :

             

                  where b.TempDate >= a.to_date and b.TempDate <= a.from_date

             

            to the SQL statement below,changing syntax as required.

            • Re: Scripting with a Master Calendar
              Chris Cammers

              It looks like you are trying to reference your Master Calendar in your SQL query. To put it simply, this just does not work the way you think.

               

              All the data you create with qlikview load statements is only available on the qlikview side of things.

               

              You could use your date range variables as part of the sql statement using dollar sign expansion to set a where clause.

               

              You could also join you calendar to the fbgbrtover table using interval match but again this would be on the qlikview side.