Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
a-5
Contributor III
Contributor III

Field 'a' not found

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!

Labels (2)
1 Solution

Accepted Solutions
Adam_Romanowski
Partner - Contributor III
Partner - Contributor III

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;

 

 

 

View solution in original post

1 Reply
Adam_Romanowski
Partner - Contributor III
Partner - Contributor III

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;