1 Reply Latest reply: Oct 19, 2015 8:19 PM by Oleg Troyansky RSS

    Troubleshooting "Field not Found"

    Chris Kelley

      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?

        • Re: Troubleshooting "Field not Found"
          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 and Qlik Sense