Skip to main content

Qlik

cancel
Turn on suggestions
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Launches Open Lakehouse and advanced agentic AI experience in Qlik Answers! | LEARN MORE
cancel
Turn on suggestions
Showing results for 
Search instead for 
Did you mean: 
krmvacar
Creator II
‎2022-08-24 07:53 AM

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)
Labels
1,464 Views
0 Likes
1 Solution

Accepted Solutions
maxgro
MVP
‎2022-08-24 11:42 AM

In response to krmvacar

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

1 user say ditto
1,436 Views
0 Likes
5 Replies
Or
MVP
‎2022-08-24 08:01 AM

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.

1,454 Views
0 Likes
krmvacar
Creator II
‎2022-08-24 08:30 AM
Author

In response to Or

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;

1,445 Views
0 Likes
Or
MVP
‎2022-08-24 08:44 AM

In response to krmvacar

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

1,438 Views
0 Likes
krmvacar
Creator II
‎2022-08-24 09:41 AM
Author

In response to Or

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
1,427 Views
0 Likes
maxgro
MVP
‎2022-08-24 11:42 AM

In response to krmvacar

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

 

1 user say ditto
1,437 Views
0 Likes
Top