Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

What's wrong with my calendar (attached below)?

What's wrong with my calendar (attached below)?

14 Replies
Sokkorn
Master
Master

Hi Friend,

Can you help to address your issue? I am very difficult to find your problem.

Regards,

Sokkorn

Anonymous
Not applicable
Author

I'm getting the following script errors when I reload

Field not found - <<=>

TempCalendar:

LOAD

+IterNo()-1 AS Num,

Date(+IterNo()-1) AS TempDate

AUTOGENERATE 1 WHILE (+IterNo()-1) <=

Table not found

MasterCalendar:

LEFT KEEP (CPI)

LOAD TempDate AS %_DateKey,

     TempDate AS %_DateKeyCA,

     week(TempDate) AS Week,

     year(TempDate) AS Year,

     month(TempDate) AS Month,

     day(TempDate) AS Day,

     weekday(TempDate) AS WeekDay,

     'Q' & ceil(month(TempDate)/3) as Quarter,

     date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

     week(TempDate)&'-'&Year(TempDate) AS WeekYear,

     Year2Date(TempDate, 0, 1, 41059)*-1 AS CurYTDFlag,

     Year2Date(TempDate,-1, 1, 41059)*-1 AS LastYTDFlag,

     num(month(TempDate)) as Period

RESIDENT TempCalendar 

ORDER BY TempDate Asc

Table not found

DROP TABLES statement

Sokkorn
Master
Master

Hi,

+IterNo() ==> Should by omit + sign. Hence try to change from +IterNo() ==> IterNo()

Regards,

Sokkorn

Anonymous
Not applicable
Author

Your variabel vMinDate and vMaxDate are empty.

That is because the Date field in your DateTemp table is not filled.

Anonymous
Not applicable
Author

Dennis,

Will you please explain further?

I don't understand exactly what you are saying

DateTemp:

LOAD (Date(Date#(Date,'YYYY/MM/DD'),'DD/MM/YYYY')) as Date      -   these date fields in bold are not filled?

RESIDENT CPI

ORDER BY Date Asc;

LET vMinDate = Num(Peek('Date', 0, 'DateTemp')); 

LET vMaxDate = Num(Peek('Date', -1, 'DateTemp'));    

LET vToday = Num(today());

what does it mean or do you mean that they are not filled?

Anonymous
Not applicable
Author

Just Type

EXIT SCRIPT;

Below the LET lines and reload your document. Then take a look at your DateTemp table.

You will see it is empty.

Your min max variables are based on that table so they are also empty. That is why you get your error.

Anonymous
Not applicable
Author

Why is the DateTemp table empty?

Is there something I can do to make it not empty?

I don't understand why it's not working because i've used the exact same Calendar script in other models and they worked just fine

see other model with same calendar script attached

Anonymous
Not applicable
Author

It is empty because your script is wrong :

This:   (Date(Date#(Date,'YYYY/MM/DD'),'DD/MM/YYYY')) as %_DateKey,   is not working.

Try this:

DateTemp:

ADD LOAD Date#(Date,'YYYY/MM/DD') as Date

RESIDENT CPI

ORDER BY Date Asc;

LET vMinDate = Peek('Date', 1, 'DateTemp');  //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable

LET vMaxDate = Peek('Date', -1, 'DateTemp');    

LET vToday = Num(today());

This should get rit of your errors.

But your date format in your data is YYYY/MM/DD and in your calander is not so also try this:

Date#(Date,'YYYY/MM/DD')  as %_DateKey,

I hope this is helpfull.

Anonymous
Not applicable
Author

hmm, still not working for me