
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Master Calendar Field 'a' not found
Hi All,
I'm trying to create a calendar, but I keep getting the same error.
Your help is welcome
Temp:
Load
min(DATE([OLPSALES.DocDate],'DD.MM.YYYY')) as minDate,
max(DATE([OLPSALES.DocDate],'DD.MM.YYYY')) as maxDate
Resident OLPSALES;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
load
$(varMinDate)+iterno()-1 as Num,
Date($(varMinDate)+iterno()-1) as TempDate
Autogenerate (1) While $(varMinDate)+iterno()-1 <=$(varMaxDate);
MasterCalendar:
LOAD [TempCalendar.TempDate] AS KEYFLDDATE,
Num#(Date([TempCalendar.TempDate], 'YYYYMMDD')) AS KEYFLDDATENUM,
Week([TempCalendar.TempDate]) AS Week,
Year([TempCalendar.TempDate]) AS Year,
Month([TempCalendar.TempDate]) AS Month,
Ceil(Month ([TempCalendar.TempDate])/1) AS MonthNum,
Day([TempCalendar.TempDate]) AS Day,
Weekday ([TempCalendar.TempDate]) AS WeekDay,
'Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS Quarter,
Year([TempCalendar.TempDate])&'-Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS YearQuarter,
'HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS HalfYear,
Year([TempCalendar.TempDate])&'-HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS YearHalfYear,
Date(Monthstart([TempCalendar.TempDate]), 'MM-YYYY') AS MonthYear,
Week([TempCalendar.TempDate]) & '-' & Year ([TempCalendar.TempDate]) AS WeekYear
Resident TempCalendar
Order by [TempCalendar.TempDate] ASC;
Drop Table TempCalendar;
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I try with this script. In bold the lines I changed.
QUALIFY *;
// test table, mon and max DocDate as your data
OLPSALES:
LOAD * INLINE [
DocDate
07.01.1999
09.01.1999
26.01.2021
28.01.2021
];
Temp:
Load
min(DATE#([OLPSALES.DocDate],'DD.MM.YYYY')) as minDate,
max(DATE#([OLPSALES.DocDate],'DD.MM.YYYY')) as maxDate
Resident OLPSALES;
Let varMinDate = Num(Peek('Temp.minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('Temp.maxDate', 0, 'Temp'));
// I trace the variables to check if they are defined
TRACE varMinDate=$(varMinDate);
TRACE varMaxDate=$(varMaxDate);
DROP Table Temp;
TempCalendar:
load
$(varMinDate)+iterno()-1 as Num,
Date($(varMinDate)+iterno()-1) as TempDate
Autogenerate (1) While ($(varMinDate)+iterno()-1) <= $(varMaxDate);
MasterCalendar:
LOAD [TempCalendar.TempDate] AS KEYFLDDATE,
Num#(Date([TempCalendar.TempDate], 'YYYYMMDD')) AS KEYFLDDATENUM,
Week([TempCalendar.TempDate]) AS Week,
Year([TempCalendar.TempDate]) AS Year,
Month([TempCalendar.TempDate]) AS Month,
Ceil(Month ([TempCalendar.TempDate])/1) AS MonthNum,
Day([TempCalendar.TempDate]) AS Day,
Weekday ([TempCalendar.TempDate]) AS WeekDay,
'Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS Quarter,
Year([TempCalendar.TempDate])&'-Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS YearQuarter,
'HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS HalfYear,
Year([TempCalendar.TempDate])&'-HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS YearHalfYear,
Date(Monthstart([TempCalendar.TempDate]), 'MM-YYYY') AS MonthYear,
Week([TempCalendar.TempDate]) & '-' & Year ([TempCalendar.TempDate]) AS WeekYear
Resident TempCalendar
Order by [TempCalendar.TempDate] ASC;
Drop Table TempCalendar;
This is the Mastercalendar


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It looks like your variables aren't getting populated correctly - if you look at the result text in the error, you'll note there's a blank where their value should be. I'd suggest you step through your code in debug mode and figure out exactly where and what the problem is.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Or ,
First of all thank you for replying,
if i try as below it works and doesn't give an error.i didn't understand problem
LET varMinDate = num('01.01.2014');
LET varMaxDate = num('31.12.' & Year(Today()));
TempCalendar:
load
$(varMinDate)+iterno()-1 as Num,
Date($(varMinDate)+iterno()-1,'DD.MM.YYYY') as TempDate
Autogenerate (1) While $(varMinDate)+iterno()-1 <=$(varMaxDate);
//*************Master Calendar************
MasterCalendar:
LOAD Date([TempCalendar.TempDate],'DD/MM/YYYY') AS KEYFLDDATE,
Num#(Date([TempCalendar.TempDate], 'YYYYMMDD')) AS KEYFLDDATENUM,
Week([TempCalendar.TempDate]) AS Week,
Year([TempCalendar.TempDate]) AS Year,
Month([TempCalendar.TempDate]) AS Month,
Ceil(Month ([TempCalendar.TempDate])/1) AS MonthNum,
Day([TempCalendar.TempDate]) AS Day,
Weekday ([TempCalendar.TempDate]) AS WeekDay,
'Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS Quarter,
Year([TempCalendar.TempDate])&'-Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS YearQuarter,
'HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS HalfYear,
Year([TempCalendar.TempDate])&'-HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS YearHalfYear,
Date(Monthstart([TempCalendar.TempDate]), 'MM-YYYY') AS MonthYear,
Week([TempCalendar.TempDate]) & '-' & Year ([TempCalendar.TempDate]) AS WeekYear
Resident TempCalendar
Order by [TempCalendar.TempDate] ASC;
Drop Table TempCalendar;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Based on that, I'd suggest checking that the underlying DocDate values are actually dates / numeric values. If they're not, Date(DocDate) won't work and the variable won't populate (You'd need to use Date#() instead).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hİ @Or ,
I try but not workıng
Temp:
Load
min(DATE#([OLPSALES.DocDate],'DD.MM.YYYY')) as minDate,
max(DATE#([OLPSALES.DocDate],'DD.MM.YYYY')) as maxDate
Resident OLPSALES;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
load
$(varMinDate)+iterno()-1 as Num,
Date($(varMinDate)+iterno()-1) as TempDate
Autogenerate (1) While $(varMinDate)+iterno()-1 <=$(varMaxDate);
MasterCalendar:
LOAD [TempCalendar.TempDate] AS KEYFLDDATE,
Num#(Date([TempCalendar.TempDate], 'YYYYMMDD')) AS KEYFLDDATENUM,
Week([TempCalendar.TempDate]) AS Week,
Year([TempCalendar.TempDate]) AS Year,
Month([TempCalendar.TempDate]) AS Month,
Ceil(Month ([TempCalendar.TempDate])/1) AS MonthNum,
Day([TempCalendar.TempDate]) AS Day,
Weekday ([TempCalendar.TempDate]) AS WeekDay,
'Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS Quarter,
Year([TempCalendar.TempDate])&'-Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS YearQuarter,
'HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS HalfYear,
Year([TempCalendar.TempDate])&'-HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS YearHalfYear,
Date(Monthstart([TempCalendar.TempDate]), 'MM-YYYY') AS MonthYear,
Week([TempCalendar.TempDate]) & '-' & Year ([TempCalendar.TempDate]) AS WeekYear
Resident TempCalendar
Order by [TempCalendar.TempDate] ASC;
Drop Table TempCalendar;
OLPSALES.DocDate |
07/01/2020 |
09/01/2020 |
10/01/2020 |
13/01/2020 |
14/01/2020 |
15/01/2020 |
16/01/2020 |
17/01/2020 |
20/01/2020 |
21/01/2020 |
22/01/2020 |
23/01/2020 |
24/01/2020 |
27/01/2020 |
28/01/2020 |
29/01/2020 |
30/01/2020 |
31/01/2020 |
03/02/2020 |
04/02/2020 |
05/02/2020 |
06/02/2020 |
07/02/2020 |
10/02/2020 |
11/02/2020 |
12/02/2020 |
14/02/2020 |
18/02/2020 |
19/02/2020 |
24/02/2020 |
26/02/2020 |
27/02/2020 |
28/02/2020 |
02/03/2020 |
05/03/2020 |
06/03/2020 |
09/03/2020 |
10/03/2020 |
12/03/2020 |
13/03/2020 |
16/03/2020 |
18/03/2020 |
20/03/2020 |
26/03/2020 |
27/03/2020 |
31/03/2020 |
01/04/2020 |
02/04/2020 |
03/04/2020 |
06/04/2020 |
09/04/2020 |
13/04/2020 |
14/04/2020 |
17/04/2020 |
20/04/2020 |
21/04/2020 |
22/04/2020 |
24/04/2020 |
26/04/2020 |
27/04/2020 |
28/04/2020 |
08/05/2020 |
11/05/2020 |
13/05/2020 |
14/05/2020 |
15/05/2020 |
18/05/2020 |
20/05/2020 |
21/05/2020 |
27/05/2020 |
03/06/2020 |
11/06/2020 |
12/06/2020 |
18/06/2020 |
29/06/2020 |
10/07/2020 |
20/07/2020 |
07/08/2020 |
13/08/2020 |
14/08/2020 |
16/08/2020 |
17/08/2020 |
18/08/2020 |
19/08/2020 |
24/08/2020 |
09/09/2020 |
05/10/2020 |
14/10/2020 |
15/10/2020 |
21/10/2020 |
05/11/2020 |
17/11/2020 |
26/11/2020 |
27/11/2020 |
30/11/2020 |
01/12/2020 |
02/12/2020 |
03/12/2020 |
04/12/2020 |
07/12/2020 |
08/12/2020 |
09/12/2020 |
10/12/2020 |
11/12/2020 |
14/12/2020 |
15/12/2020 |
16/12/2020 |
17/12/2020 |
18/12/2020 |
22/12/2020 |
25/12/2020 |
30/12/2020 |
31/12/2020 |
07/01/2021 |
08/01/2021 |
11/01/2021 |
12/01/2021 |
13/01/2021 |
14/01/2021 |
15/01/2021 |
26/01/2021 |
28/01/2021 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I try with this script. In bold the lines I changed.
QUALIFY *;
// test table, mon and max DocDate as your data
OLPSALES:
LOAD * INLINE [
DocDate
07.01.1999
09.01.1999
26.01.2021
28.01.2021
];
Temp:
Load
min(DATE#([OLPSALES.DocDate],'DD.MM.YYYY')) as minDate,
max(DATE#([OLPSALES.DocDate],'DD.MM.YYYY')) as maxDate
Resident OLPSALES;
Let varMinDate = Num(Peek('Temp.minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('Temp.maxDate', 0, 'Temp'));
// I trace the variables to check if they are defined
TRACE varMinDate=$(varMinDate);
TRACE varMaxDate=$(varMaxDate);
DROP Table Temp;
TempCalendar:
load
$(varMinDate)+iterno()-1 as Num,
Date($(varMinDate)+iterno()-1) as TempDate
Autogenerate (1) While ($(varMinDate)+iterno()-1) <= $(varMaxDate);
MasterCalendar:
LOAD [TempCalendar.TempDate] AS KEYFLDDATE,
Num#(Date([TempCalendar.TempDate], 'YYYYMMDD')) AS KEYFLDDATENUM,
Week([TempCalendar.TempDate]) AS Week,
Year([TempCalendar.TempDate]) AS Year,
Month([TempCalendar.TempDate]) AS Month,
Ceil(Month ([TempCalendar.TempDate])/1) AS MonthNum,
Day([TempCalendar.TempDate]) AS Day,
Weekday ([TempCalendar.TempDate]) AS WeekDay,
'Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS Quarter,
Year([TempCalendar.TempDate])&'-Q' & Ceil(Month ([TempCalendar.TempDate])/3) AS YearQuarter,
'HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS HalfYear,
Year([TempCalendar.TempDate])&'-HY' & Ceil(Month ([TempCalendar.TempDate])/6) AS YearHalfYear,
Date(Monthstart([TempCalendar.TempDate]), 'MM-YYYY') AS MonthYear,
Week([TempCalendar.TempDate]) & '-' & Year ([TempCalendar.TempDate]) AS WeekYear
Resident TempCalendar
Order by [TempCalendar.TempDate] ASC;
Drop Table TempCalendar;
This is the Mastercalendar
