Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
krmvacar
Creator II

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;

krmvacar_0-1661341949598.png

 

Labels (3)
1 Solution

Accepted Solutions
maxgro
MVP

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

maxgro_0-1661355612856.png

 

View solution in original post

5 Replies
Or
MVP

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.

krmvacar
Creator II
Author

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;

Or
MVP

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).

krmvacar
Creator II
Author

@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
maxgro
MVP

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

maxgro_0-1661355612856.png