Troubleshooting "Field not Found"

[Anonymous]

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?

[Oleg_Troyansky]

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...