
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Master Calendar
Hi Guys,
I am new to QlikView and still learning the tool. I am currently having issue with my Master Calendar. This script is working fine in one of my older apps but when I try to replicate it in my newer app, it is failing. Please guide me on how to fix this.
QuartersMap:
Mapping LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AutoGenerate (12);
Temp:
LOAD
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident ORDER;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
TempCalendar:
LOAD
$(varMinDate) + IterNo()-1 as Num,
Date($(varMinDate) +IterNo()-1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo()-1 <= $(varMaxDate);
DROP Table Temp;
OrderCalendar:
LOAD
TempDate as OrderDate,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order by TempDate asc;
Drop Table TempCalendar;
Error:
Field not found - <<=>
TempCalendar:
LOAD
+ IterNo()-1 as Num,
Date( +IterNo()-1) as TempDate
AutoGenerate 1 While + IterNo()-1 <=
Any help would be greatly appreciated!
Thanks,
ZQ

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
there are records in ORDER table?
I checked your script with a test ORDER table and it works
// this is the test ORDER I used, before your script
ORDER: load date(makedate(2015) + rowno() -1) as OrderDate AutoGenerate 730;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In addition also make sure that OrderDate is read as date by QlikView. In case it isn't you might have to use Date#() function to help QlikView understand your date format.Why don’t my dates work?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Maxgro,
Yes table and OrderDate field both have data. When I replace this Let varMinDate = Num(Peek('minDate', 0, 'Temp')) and
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); with 99999 or 88888, the Master Calendar works. However when I use minDate or maxDate, it doesn't work.

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI,
I think you are not getting any values in the variables varMinDate & varMaxDate . Please check whether OrderDate is a valid date.
Temp:
LOAD
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident ORDER;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
Regards,
jagan.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear Zeeshan,
Your Master Calendar script looks fine.
I think you need to check your OrderDate and it's format before to assign values to variables.
Can you confirm either you are placing whole Master Calendar script on a same script tab or not?
One more thing, please check your resident table "ORDER" as well.
Kind regards,
Ishfaque Ahmed

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jagan,
yes I agree with you. I don't think variables are not getting any values. what should I do to fix this?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ishfaque,
Master Calendar Script is on a different tab. The ORDER table is fine. I have checked the Orderdate and the format is accurate.
Regards,
Zeeshan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Zeeshan,
I will recommend to deeply check your OrderDate format you are loading:
Here is a very good link from Henric for dates common issues in QV and how to check and fix them:
https://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work
Kind regards
Maurice

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You have to convert the date field to a valid date format using Date() & Date#() then only your script works. Also if possible attach some sample data in QVD or share sample file.
Regards,
Jagan.
