Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
What's wrong with my calendar (attached below)?
Hi Friend,
Can you help to address your issue? I am very difficult to find your problem.
Regards,
Sokkorn
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
Hi,
+IterNo() ==> Should by omit + sign. Hence try to change from +IterNo() ==> IterNo()
Regards,
Sokkorn
Your variabel vMinDate and vMaxDate are empty.
That is because the Date field in your DateTemp table is not filled.
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?
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.
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
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.
hmm, still not working for me