Master Calendar

[#1]

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

[maxgro]

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;

[sunny_talwar]

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?

[#4]

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.

[jagan]

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.

[engishfaque]

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

[#7]

Jagan,

yes I agree with you. I don't think variables are not getting any values. what should I do to fix this?

[#8]

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

[Anonymous]

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

[jagan]

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.