10 Replies Latest reply: Mar 26, 2018 3:41 AM by Gérald LEBRET RSS

    Field not found in loading script

    Wim Embrechts

      Hi,

       

      I was making a master Calendar and when loading the script I got the error "Field 'a' not found". The script error appeared when I added the bold text. The first load statement of the bold text is the preceding load of the last load statement.

       

      Does anyone sees what's wrong with the script?

      regards,

      Wim

       

       

      Temp_Calendar_Range:

      Load

      num(floor(date(min(DATUM), 'dd/MM/yyyy'))) as MinDate,

        num(floor(date(max(DATUM), 'dd/MM/yyyy'))) as MaxDate

      resident TelWerkgroepstats;

       

       

      //Assign  the start and end dates to variables

      let vMinDate = Peek('Mindate', 0, 'Temp_Calender_Range');

      let vMaxDate = Peek('Maxdate', 0, 'Temp_Calender_Range');

       

       

      drop table  Temp_Calendar_Range; //Cleanup

       

       

      MasterCalendar:

      LOAD DISTINCT

          Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

          Year(Temp_Date) as [Year],

          Month(Temp_Date) as [Month],

          Date(Temp_Date, 'YYYY-MM') as [Year - Month],

          'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

       

       

      ;

      LOAD DISTINCT

          MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

          AUTOGENERATE (1)

          WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

       

       

      //--- Remove the temporary variables

      LET vMinDate = Null();

      LET vMaxDate = Null();

        • Re: Field not found in loading script
          omar bensalem

          Can you try as follow?

          Let varMinDate = Num(Peek('MinDate', 0, 'Temp_Calendar_Range')); 

          Let varMaxDate = Num(Peek('MaxDate', 0, 'Temp_Calendar_Range')); 

          //DROP Table Temp_Calendar_Range; 

           

          TempCalendar: 

          LOAD 

                         $(varMinDate) + Iterno()-1 As Num, 

                         Date($(varMinDate) + IterNo() - 1) as Temp_Date 

                         AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

           

           

           

          drop table  Temp_Calendar_Range; //Cleanup

           

           

          MasterCalendar:

          LOAD DISTINCT

              Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

              Year(Temp_Date) as [Year],

              Month(Temp_Date) as [Month],

              Date(Temp_Date, 'YYYY-MM') as [Year - Month],

              'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

           

          ;

          //--- Remove the temporary variables

          LET varMinDate = Null();

          LET varMaxDate = Null();

          • Re: Field not found in loading script
            Devarasu R

            Hi,

            You code looks ok except storing the date variable. just remember that qlik is case sensitive MinDate  & MaxDate

            Temp_Calendar_Range:

            Load

            num(floor(date(min(DATUM), 'dd/MM/yyyy'))) as MinDate,

              num(floor(date(max(DATUM), 'dd/MM/yyyy'))) as MaxDate

            resident TelWerkgroepstats;

             

            //Assign  the start and end dates to variables

            let vMinDate = Peek('MinDate', 0, 'Temp_Calender_Range');

            let vMaxDate = Peek('MaxDate', 0, 'Temp_Calender_Range');

             

            drop table  Temp_Calendar_Range; //Cleanup

             

            MasterCalendar:

            LOAD DISTINCT

                Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

                Year(Temp_Date) as [Year],

                Month(Temp_Date) as [Month],

                Date(Temp_Date, 'YYYY-MM') as [Year - Month],

                'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

            ;

            LOAD DISTINCT

                MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

                AUTOGENERATE (1)

                WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

             

            //--- Remove the temporary variables

            LET vMinDate = Null();

            LET vMaxDate = Null();

             

            P.S: moving forward try to use that revised master calendar script (it's much faster than your current code.

            Revised master calendar - store min/max date in... | Qlik Community

            Better Calendar Scripts | Qlikview Cookbook

             

            Thanks

            Deva

            • Re: Field not found in loading script
              Mark Little

              HI

               

              If I remember right, I think with preceding loads the Distinct only work on the last part of load.

               

              I would give the below a try.

               

              MasterCalendar:

              LOAD DISTINCT

                  Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

                  Year(Temp_Date) as [Year],

                  Month(Temp_Date) as [Month],

                  Date(Temp_Date, 'YYYY-MM') as [Year - Month],

                  'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

               

               

              ;

              LOAD

                  MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

                  AUTOGENERATE (1)

                  WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);


              Mark

              • Re: Field not found in loading script
                Gérald LEBRET

                HI,

                I am getting the same result  (Field 'a' not found).

                I noticed 2 problems, and I can't figure out why.

                Here is my script:

                 

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

                Temp:
                Load
                min(Date_Valeur) as minDate,
                    max(Date_Valeur) as maxDate
                Resident FACT;

                LET varMinDate=NUM(peek(minDate,0,'Temp'));
                LET varMaxDate=NUM(peek(maxDate,0,'Temp'));

                LET varToday=Num(Today());

                Drop Table Temp;

                //
                ////Trace Date de début $(varMinDate);
                ////Trace Date de fin $(varMaxDate);
                //

                TempCalendar:
                LOAD
                $(varMinDate) + IterNo() - 1 AS Num, // Format numérique
                Date($(varMinDate) + IterNo() - 1) AS TempDate //Format Date
                AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
                //
                ////**********MasterCalendar ***************

                CAL:
                load
                TempDate AS Date_Valeur,
                Week (TempDate) AS Semaine,
                Year(TempDate) AS Annee,
                Month(TempDate) AS Mois,
                    Date(MonthStart(TempDate), 'MMM.YYYY') AS Periode,
                Day(TempDate) AS Jour,
                WeekDay(TempDate) AS Jour_Semaine,
                  'Q'&Ceil( Month((TempDate)/3)) as Trimestre,
                  Week(weekstart(TempDate))&'-'&WeekYear(TempDate) AS Semaine_Annee,
                WeekDay(TempDate) as WeekDay
                    Resident TempCalendar
                ORDER BY TempDate ASC;

                drop table TempCalendar;

                 

                Can anyone help?

                 

                Thank you.