Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have had a look at the Master Calendar video as well as I read the previous discussion but still can't solve my problem.
I have a simple Direct query that looks like following:
Aggregation:
LIB CONNECT TO 'REPORTING';
DIRECT QUERY
DIMENSION
NATIVE('CONVERT(DATE, ActivityDate)') AS ActivityDay
MEASURE
NumberOfSales
FROM <table>
And then I copied the master calendar script replacing this:
Temp:
Load
min(ActivityDay) as minDate,
max(ActivityDay) as maxDate
Resident Aggregation;
But I'm still getting fiel not found error.. any idea why?
(Field not found - <<=>: TempCalendar: LOAD + Iterno()-1 As Num, Date( + IterNo() - 1) as TempDate AutoGenerate 1 While + IterNo() -1 <=)
Hi,
Can you paste the whole master calendar generation script?
Regards,
Jagan.
This script looks okay, error might be in TempCalender table as highlighted by Qlik Engine
(Field not found - <<=>: TempCalendar: LOAD + Iterno()-1 As Num, Date(DateField is Missing here + IterNo() - 1) as TempDate AutoGenerate 1 While + IterNo() -1 <=)
Share the script of TempCalender Table
There it is:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(ActivityDay) as minDate,
max(ActivityDay) as maxDate
Resident Aggregation;
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
TempDate AS ActivityDay,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & (TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By ActivityDay ASC;
Drop Table TempCalendar;
The data load doesn't fail now, but how do I join this to my Aggregation table?
If I rename the "date" field to match my aggregation I guess the link is created automatically but if I place Week or Month on the visualisation, it's empty?
During data load it says CalendarMaster << Aggregation
Lines fetched: 0
even though the aggregation data loaded correctly
Hi Dawid,
Try this script:
Thanks,
Sangram.
Hi,
I think the date format is not the same in both the tables, check it and adjust accordingly.
Regards,
jagan.
NO TABLE FOUND showing error