Master calendar - field not found

[#1]

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

[jagan]

Hi,

Can you paste the whole master calendar generation script? 

Regards,

Jagan.

[Anonymous]

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

[#4]

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; 

[jagan]

Hi,

Check this link

Master Calendar Generation Script

Regards,

jagan.

[#6]

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

[reddy-s]

Hi Dawid,

Try this script:

1. QuartersMap: 
2. MAPPING LOAD  
3. rowno() as Month, 
4. 'Q' & Ceil (rowno()/3) as Quarter 
5. AUTOGENERATE (12); 
6.  
7. Temp: 
8. Load 
9.                min(OrderDate) as minDate, 
10.                max(OrderDate) as maxDate 
11. Resident Orders; 
12.  
13. Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
14. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
15. DROP Table Temp; 
16.  
17. TempCalendar: 
18. LOAD 
19.                $(varMinDate) + Iterno()-1 As Num, 
20.                Date($(varMinDate) + IterNo() - 1) as TempDate 
21.                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
22.  
23. MasterCalendar: 
24. Load 
25.                TempDate AS OrderDate, 
26.                week(TempDate) As Week, 
27.                Year(TempDate) As Year, 
28.                Month(TempDate) As Month, 
29.                Day(TempDate) As Day, 
30.                YeartoDate(TempDate)*-1 as CurYTDFlag, 
31.                YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
32.                inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
33.                date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
34.                ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
35.                Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
36.                WeekDay(TempDate) as WeekDay 
37. Resident TempCalendar 
38. Order By TempDate ASC; 
39. Drop Table TempCalendar; 

       Thanks,

        Sangram.

[jagan]

Hi,

I think the date format is not the same in both the tables, check it and adjust accordingly.

Regards,

jagan.

[Dolly123]

NO TABLE FOUND showing error