"Field 'a' not found" Error

[CreepyCatLady]

I am getting an error when trying to load my script, and the error text is completely unhelpful. I have scoured my code and I cannot find the issue. I did a line-by-line debug and that was also unhelpful. I though perhaps a few fresh sets of eyes could see what I am missing. I've attached a screenshot of the error, and my load script is below. Thanks for the help.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
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';

LIB CONNECT TO '[redacted for security reasons]';

LOAD ID,
LocalDate,
LocalTime,
LocalTimeZone,
AppName,
DisplayName,
AppVersion,
AppExecutions,
AppRunTime,
AppRunTimeInSeconds,
UserDomain,
UserName,
UserFullName,
DeviceDomain,
DeviceName,
CatalogName,
TargetEntityName,
Details,
LogFile;

MokshaUsage:
SELECT ID,
LocalDate,
LocalTime,
LocalTimeZone,
AppName,
DisplayName,
AppVersion,
AppExecutions,
AppRunTime,
AppRunTimeInSeconds,
UserDomain,
UserName,
UserFullName,
DeviceDomain,
DeviceName,
CatalogName,
TargetEntityName,
Details,
LogFile
FROM [redacted for security reasons];

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(LocalDate) as minDate,
max(LocalDate) as maxDate
Resident MokshaUsage;

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 LocalDate,
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)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

[Taoufiq_Zarra]

@CreepyCatLady  it seems to me that it's no error just verifie LocalDate format in the MokshaUsage table

for example you can add Date#(MokshaUsage,'M/D/YYYY'') as MokshaUsage, if your source format is M/D/YYYY'

[stevedark · Accepted solution]

Hi @CreepyCatLady 

I'm not sure why it is complaining about a field called a, but the issue is that the variable varMinDate contains nothing at the point you are creating the tempCalendar table.

My best guess of why this would be is that LocalDate does not contain a valid date field. If you are loading from a text file then the date will need converting.

Stick a line after the first FROM statement, like this:

exit script;

This will allow the script to finish at that point and you can view what you have in the LocalDate field in a filter pane.

You will most likely need to convert it, based on the format of the date in the filter pane, so if it is 2020-09-22, for instance you would have;

LOAD
ID,
Date(Date#(LocalDate, 'YYYY-MM-DD'), 'DD MMM YYYY') as LocalDate,
LocalTime
...

Once that is done you can remove the exit script and try again.

The other thing I have noticed is that you are loading 17M rows. Doing the max and min of the date will take ages like that. You may be better creating the date parts inline in the table, rather than having a separate calendar.

Hope that helps.

Steve