topic Re: Master Calendar in QlikView

Master Calendar

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: Master Calendar

maxgro — Wed, 09 Mar 2016 23:32:58 GMT

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;

Re: Master Calendar

sunny_talwar — Thu, 10 Mar 2016 00:38:37 GMT

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?

Re: Master Calendar

Thu, 10 Mar 2016 00:51:23 GMT

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.

Re: Master Calendar

jagan — Thu, 10 Mar 2016 01:32:07 GMT

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.

Re: Master Calendar

engishfaque — Thu, 10 Mar 2016 03:24:14 GMT

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

Re: Master Calendar

Thu, 10 Mar 2016 16:51:39 GMT

Jagan,

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

Re: Master Calendar

Thu, 10 Mar 2016 16:54:38 GMT

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

Re: Master Calendar

Anonymous — Thu, 10 Mar 2016 17:24:45 GMT

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

Re: Master Calendar

jagan — Fri, 11 Mar 2016 00:56:21 GMT

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.