Scripting with a Master Calendar

[raynac]

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.

[mdmukramali]

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.

[Anonymous]

Maybe move :

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

to the SQL statement below,changing syntax as required.

[chriscammers · Accepted solution]

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.

[raynac]

Hi Chris,

Again the collective wisdom and kindness of this group has helped me immensely.  Thank you for pointing me in the right direction...that being IntervalMatch.  Using the blog post that Bill Markham posted and then, apparently, subsequently deleted, I was able to make this work perfectly.

For future reference, this is the blog post I used to get this to work.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Thanks to you both for saving me what I believe would have been a significant amount of time.