Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chkelley
New Contributor III

Troubleshooting "Field not Found"

I have the following script in Sense:

calendar:

LOAD

               min([CalendarDate]) as minDate,

               max([CalendarDate]) as maxDate

Resident telemetry;

LOAD

    %DATE_KEY,

  CalendarDate,

    CalendarDayOfMonth,

    CalendarDayName,

    CalendarWeekOfYear,

    CalendarMonthName,

    CalendarQuarter,

    CalendarYear,

    CalendarWeekNumberAndYear,

    CalendarMonthAndYear,

    CalendarQuarterMonthsAndYear,

    CalendarDayStart,

    CalendarWeekStart,

    CalendarMonthStart,

    CalendarQuarterStart,

    CalendarYearStart,

    CalendarDayEnd,

    CalendarWeekEnd,

    CalendarMonthEnd,

    CalendarQuarterEnd,

    CalendarYearEnd,

    CalendarQuarterAndYear,

    CalendarYearAndQuarter,

    CalendarWednesdays,

  %Fiscal_Key,

    FiscalMonthNum,

    FiscalYearDesc,

    FYRPR,

    FiscalYear,

    FiscalYearQuarter

FROM [lib://XXX/MASTER_CALENDAR_DIM.qvd]

(qvd)

WHERE CalendarDate => minDate AND CalendarDate <= [maxDate];

It dies on a field not found error, at the WHERE statement. Seems it does not like minDate or maxDate, and I can't seem to be able to find a way to get Sense to point to the minDate and maxDate values called earlier in the Table script.

Any ideas?

1 Reply
MVP
MVP

Re: Troubleshooting "Field not Found"

Hi Chris,

fields from a data table cannot be used directly in another table load. Instead, you should "peek" the field values into variables, and then use the variables in the subsequent LOAD. Something like this:

  1. calendar: 
  2. LOAD  
  3.                min([CalendarDate]) as minDate,  
  4.                max([CalendarDate]) as maxDate  
  5. Resident telemetry;  
  6.  
  7. LET v_MinDate = Peek('minDate', 0, 'calendar');
  8. LET v_MaxDate = Peek('maxDate', 0, 'calendar');
  9.  
  10. LOAD 
  11.     %DATE_KEY, 
  12.   CalendarDate, 
  13.     CalendarDayOfMonth, 
  14.     CalendarDayName, 
  15.     CalendarWeekOfYear, 
  16.     CalendarMonthName, 
  17.     CalendarQuarter, 
  18.     CalendarYear, 
  19.     CalendarWeekNumberAndYear, 
  20.     CalendarMonthAndYear, 
  21.     CalendarQuarterMonthsAndYear, 
  22.     CalendarDayStart, 
  23.     CalendarWeekStart, 
  24.     CalendarMonthStart, 
  25.     CalendarQuarterStart, 
  26.     CalendarYearStart, 
  27.     CalendarDayEnd, 
  28.     CalendarWeekEnd, 
  29.     CalendarMonthEnd, 
  30.     CalendarQuarterEnd, 
  31.     CalendarYearEnd, 
  32.     CalendarQuarterAndYear, 
  33.     CalendarYearAndQuarter, 
  34.     CalendarWednesdays, 
  35.   %Fiscal_Key, 
  36.     FiscalMonthNum, 
  37.     FiscalYearDesc, 
  38.     FYRPR, 
  39.     FiscalYear, 
  40.     FiscalYearQuarter 
  41. FROM [lib://XXX/MASTER_CALENDAR_DIM.qvd] 
  42. (qvd) 
  43. WHERE CalendarDate => $(v_MinDate) AND CalendarDate <= $(v_MaxDate); 

If you'd like to learn more about variables and other advanced data load features, check out my new book QlikView Your Business.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView an...