Master Calendar - Field 'a' not found Error

SAD93 — Mon, 07 Nov 2022 12:52:56 GMT

Hello All,

this my first post here in this website but i have learend alot from it, but know im facing problem with master calender as this message pop up when i tried to load the script

"The following error occurred:

Field 'a' not found

The error occurred here:

TempCalendar: LOAD + Iterno()-1 As Num,"

and here is my script :

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min("INVOICE_DATE") as minDate,

max("INVOICE_DATE") as maxDate

Resident Collection_Dashboard;

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_Collection:

Load

TempDate AS "INVOICE_DATE",

date( floor(Timestamp(TempDate, 'MM/DD/YYYY hh:mm:ss TT')),'DD/MM/YYYY') as "Date",

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,

// inmonth(today(),TempDate,0)*-1 as MTD_FLAG,

// InMonthToDate( TempDate,AddYears(($(vDateToday)),-1),0)*-1 as LYMTD_FLAG,

If( TempDate - YearStart(TempDate) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD,

if( Day(TempDate)= today(),

if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()),1,0) ,

if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()-1),1,0)) as IsInMTD,

if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()-1),1,0) as IsInLYMTD,

// InMonthToDate(TempDate,$(vDateToday),-1,4)*-1 as LMTD_FLAG,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

// num(Year(TempDate) )+ num( Month(TempDate)) as "MonthYear Num",
num(Year(TempDate) )& num( Month(TempDate),'00') as "MonthYear Num",
num(Year(TempDate) )& num( Month(TempDate),'00')& num( Day(TempDate),'00') as "MonthYearDay Num",

num(Year(TempDate) )& num( Month(TempDate),'00')& num( Week(TempDate),'00') as "MonthYearWeek Num",

num(Year(TempDate)-1 )& num( Month(TempDate),'00') & num( Day(TempDate),'00') as "LYMTD_Date_Num",

// floor( date#( date( floor(Timestamp(TempDate, 'MM/DD/YYYY hh:mm:ss TT')),'YYYYMMY'),'YYYYMMY')) as "MonthYear Num",

// ( date(TempDate, 'YYYYMM')) as "MonthYear Num",

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Join

load
1 as "MAx MTD",
date(max("Date"),'DD/MM/YYYY') as "MAX MTD Date",
"MonthYear Num"

Resident
MasterCalendar_Collection
where IsInMTD = 1
Group by "MonthYear Num";

Drop Table TempCalendar;

store Collection_Dashboard into [lib://Sadeem (lebara_qlik.admin)/Collection_Dashboard.qvd](qvd);
store MasterCalendar_Collection into [lib://Sadeem (lebara_qlik.admin)/MasterCalendar_Collection.qvd](qvd);

exit script;

Re: Master Calendar - Field 'a' not found Error

marcus_sommer — Mon, 07 Nov 2022 14:09:00 GMT

It means that by:

$(varMinDate) + Iterno()-1 As Num

the variable $(varMinDate) has no valid value else is NULL. This might be caused from your INVOICE_DATE which might be not interpreted numeric else as a string which would affect your min/max aggregation as well as the num() wrapping of the peek().

Another cause could be that INVOICE_DATE isn't a date else a timestamp. In this case the value has a decimal-delimiter and if the delimiter is a comma the variable would interpret it as delimiter for the variable itself and not for the value and again the result would be NULL.

- Marcus

topic Re: Master Calendar - Field 'a' not found Error in Connectivity & Data Prep

Master Calendar - Field 'a' not found Error

Re: Master Calendar - Field 'a' not found Error