Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Master calendar - field not found

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

7 Replies
MVP
MVP

Re: Master calendar - field not found

Hi,

Can you paste the whole master calendar generation script? 

Regards,

Jagan.

balrajahlawat
Esteemed Contributor

Re: Master calendar - field not found

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

Not applicable

Re: Master calendar - field not found

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; 

MVP
MVP

Re: Master calendar - field not found

Hi,

Check this link

Master Calendar Generation Script

Regards,

jagan.

Not applicable

Re: Master calendar - field not found

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

reddys310
Honored Contributor II

Re: Master calendar - field not found

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.

MVP
MVP

Re: Master calendar - field not found

Hi,

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

Regards,

jagan.

Community Browser