topic Re: Master calendar - field not found in Visualization and Usability

Master calendar - field not found

Thu, 25 Feb 2016 23:07:53 GMT

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

Re: Master calendar - field not found

jagan — Fri, 26 Feb 2016 02:48:36 GMT

Hi,

Can you paste the whole master calendar generation script?

Regards,

Jagan.

Re: Master calendar - field not found

Anonymous — Fri, 26 Feb 2016 05:05:44 GMT

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

Re: Master calendar - field not found

Fri, 26 Feb 2016 07:57:57 GMT

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;

Re: Master calendar - field not found

jagan — Fri, 26 Feb 2016 08:36:32 GMT

Hi,

Check this link

Master Calendar Generation Script

Regards,

jagan.

Re: Master calendar - field not found

Fri, 26 Feb 2016 09:43:39 GMT

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

Re: Master calendar - field not found

reddy-s — Fri, 26 Feb 2016 12:48:23 GMT

Hi Dawid,

Try this script:

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident Orders;
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 OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Thanks,

Sangram.

Re: Master calendar - field not found

jagan — Mon, 29 Feb 2016 00:39:13 GMT

Hi,

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

Regards,

jagan.

Re: Master calendar - field not found

Dolly123 — Sat, 05 Mar 2022 18:00:19 GMT

NO TABLE FOUND showing error