Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

MikeD
New Contributor

MasterCalendar error

Hi to everyone,

I'm trying to use master calendar created using this script:

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min([create_time]) as minDate,
max([create_time]) as maxDate
Resident ticket;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + IterNo()- 1 As Num,
$(varMinDate) + IterNo() - 1 as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS GlobalDate,
Date(TempDate, 'DD/MM/YYYY') AS CalendarDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Year(TempDate) & '-' & Month(TempDate) as YearMonth,
Month(TempDate) As Month,
Day(TempDate) As Day,
Year(TempDate) & '-' & ApplyMap('QuartersMap', month(TempDate), Null()) as YearQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as YearWeek,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

When load data I obtain the following error message....I cannot understand why the ',' is wrongly considered as an operator and how fix it....

Si è verificato il seguente errore:
Unexpected token: ',', expected one of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and', ...
 
L'errore si è verificato qui:
TempCalendar: LOAD 41400,493252315 + Iterno()- 1 As Num, 41400,493252315 + IterNo() - 1 as TempDate AutoGenerate 1 While 41400>>>>>>,<<<<<<493252315 + IterNo() -1 <= 43584,531284722
 
 
 
Many thanks in advance.
Mike
3 Replies
Partner
Partner

Re: MasterCalendar error

Hi,

As to my understanding, the lines:
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

May not be returning anything, so the next load is getting an error.
Can you check that both variables have values please?

I've tested the script here:

dummy:
load * Inline
[
type,value,data
red,100,01/01/2019
green,200,02/01/2019
blue,300,03/01/2019

];

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(data) as minDate,
max(data) as maxDate
Resident dummy;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + IterNo()- 1 As Num,
$(varMinDate) + IterNo() - 1 as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS GlobalDate,
Date(TempDate, 'DD/MM/YYYY') AS CalendarDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Year(TempDate) & '-' & Month(TempDate) as YearMonth,
Month(TempDate) As Month,
Day(TempDate) As Day,
Year(TempDate) & '-' & ApplyMap('QuartersMap', month(TempDate), Null()) as YearQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as YearWeek,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

and went like a charm.

MikeD
New Contributor

Re: MasterCalendar error

Hi Felipedl,

thanks for you answer.

In my ticket table I have create_time with values...

values.png 

MikeD
New Contributor

Re: MasterCalendar error

Hi, I don't know why but starting from the beginning again solve the problem!

I have another question. I have three tables:
ticket with create_time
ticket_log with close_time
mastercalendar

I have linked master calendar date with both the previous table....and I want to create a line chart using master calendar date as dimension and create_time and close_time as two different measure....is it possibile?