Field 'a' not found

[a-5]

Hi,

I got the following error and not sure what this is all about:

The following error occurred:

Field 'a' not found

The error occurred here:

TEMP_CALENDAR:

Load

+ IterNo()-1 as Num,

Date( + IterNo()-1) AS TempDate

AutoGenerate 1 While + IterNo()-1 <=

Here's my code:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='#,##0.00€;-#,##0.00€';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=0;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

Let vToday=Date(Today(),'DD.MM.YYYY');

QUARTERS_MAP:
Mapping Load
RowNo() As Month,
'Q' & Ceil (RowNo()/3) AS Quarter
AutoGenerate(12);

TEMP:
LOAD
YearStart(AddYears('$(vToday)',-3)) as minDate,
YearEnd('$(vToday)') as maxDate;

Let varMinDate= Num(Peek('minDate',0,'Temp'));
Let varMaxDate= Num(Peek('maxDate',0,'Temp'));
// DROP TABLE TEMP;

TEMP_CALENDAR:
Load
$(varMinDate) + IterNo()-1 as Num,
Date($(varMinDate) + IterNo()-1) AS TempDate
AutoGenerate 1 While $(varMinDate) + IterNo()-1 <= $(varMaxDate);

MASTER_CALENDAR:
LOAD
TempDate AS Dateday,
DATE(MonthStart(TempDate),'YYYY-MM') AS MonthKey,
ApplyMap('QUARTERS_MAP',Month(TempDate),Null()) As Quarter,
Month(TempDate) AS Month,
Year(TempDate)&Num(Month(TempDate),00) AS YearMonth,
Year(TempDate)&'-'&Num(Month(TempDate),00) AS Period,
Year(TempDate)&'-'&ApplyMap('QUARTERS_MAP',Month(TempDate),Null()) AS YearQuarter
RESIDENT TEMP_CALENDAR
ORDER BY TempDate ASC;
DROP TABLE TEMP_CALENDAR;

Any advise how to solve this? Thank you so much!

[Adam_Romanowski · Accepted solution]

Hi, you have declared preceeding LOAD (table TEMP) but haven't declared base table which should be used in preceeding loads. This means, that table TEMP does not exist. Write exit script just after declaring table TEMP and you will notice that data model is empty.

So, if table TEMP does not exist, Peek() function will return NULL on minDate,maxDate fields - and this means that variables vMinDate, vMaxDate don't exist. You have also typo in Peek function() - you have tried to declare table TEMP, but in Peek() you try to get values from table Temp (field/table names are case sensitive).

So i fixed your code by declaring variables varMinDate, varMaxDate without using Peek.

Let vToday=Date(Today(),'DD.MM.YYYY');

QUARTERS_MAP:
Mapping Load
RowNo() As Month,
'Q' & Ceil (RowNo()/3) AS Quarter
AutoGenerate(12);

// TEMP:
// LOAD
// YearStart(AddYears('$(vToday)',-3)) as minDate,
// YearEnd('$(vToday)') as maxDate;

Let varMinDate= num(YearStart(AddYears(vToday,-3)));
Let varMaxDate= num(floor(YearEnd(vToday)));
// DROP TABLE TEMP;

TEMP_CALENDAR:
Load
$(varMinDate) + IterNo()-1 as Num,
Date($(varMinDate) + IterNo()-1) AS TempDate
AutoGenerate 1 While $(varMinDate) + IterNo()-1 <= $(varMaxDate);

MASTER_CALENDAR:
LOAD
  TempDate AS Dateday,
  DATE(MonthStart(TempDate),'YYYY-MM') AS MonthKey,
  ApplyMap('QUARTERS_MAP',Month(TempDate),Null()) As Quarter,
  Month(TempDate) AS Month,
  Year(TempDate)&Num(Month(TempDate),00) AS YearMonth,
  Year(TempDate)&'-'&Num(Month(TempDate),00) AS Period,
  Year(TempDate)&'-'&ApplyMap('QUARTERS_MAP',Month(TempDate),Null()) AS YearQuarter
RESIDENT TEMP_CALENDAR
ORDER BY TempDate ASC;
DROP TABLE TEMP_CALENDAR;

exit script;