MasterCalendar error

[MikeD]

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

[felipedl]

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]

Hi Felipedl,

thanks for you answer.

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

 

[MikeD]

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?