8 Replies Latest reply: Sep 29, 2015 9:16 AM by Ahmet Akin RSS

    Help with Master calendar script error in the Qlik Sense

    Ahmet Akin

      Hi Im trying to create a master calendar script in qlik sense but i get the following error :

       

      What is the source of problem here ?

       

      Error Message :

       

       

       

      Started loading data

      Test << data Lines fetched: 1 000 QuartersMap << AUTOGENERATE(12) Lines fetched: 12 Temp << Test Lines fetched: 1

      The following error occurred:

      Field not found - <<=>

      The error occurred here:

      TempCalendar: LOAD + Iterno()-1 As Num, Date( + IterNo() - 1) as TempDate AutoGenerate 1 While + IterNo() -1 <=

       

       

       

      My Script :

       

      SET ThousandSep=' ';

      SET DecimalSep=',';

      SET MoneyThousandSep='.';

      SET MoneyDecimalSep=',';

      SET MoneyFormat='#.##0,00 kr;-#.##0,00 kr';

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='YYYY-MM-DD';

      SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

      SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';

      SET DayNames='mån;tis;ons;tor;fre;lör;sön';

      SET LongMonthNames='januari;februari;mars;april;maj;juni;juli;augusti;september;oktober;november;december';

      SET LongDayNames='måndag;tisdag;onsdag;torsdag;fredag;lördag;söndag';

      SET FirstWeekDay=0;

      SET BrokenWeeks=0;

      SET ReferenceDay=4;

      SET FirstMonthOfYear=1;

      SET CollationLocale='sv-SE';

       

      Test:

      LOAD

          id,

          firstname,

          surname,

          email,

          country,

          city,

          Longitude,

          Latitude,

          GeoMakePoint(Latitude,Longitude) as GeoKey,

          title,

          nhours,

          amount,

          dates

      FROM [lib://new/MOCK_DATA(2).xlsx]

      (ooxml, embedded labels, table is data);

       

       

       

      QuartersMap:

      MAPPING LOAD

      rowno() as Month,

      'Q' & Ceil (rowno()/3) as Quarter

      AUTOGENERATE (12);

       

       

      Temp:

      Load

      min(dates) as minDate,

      max(dates) as maxDate     

      Resident [Test];

       

      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

      Let vToday = Today();

       

      Drop Table Temp;

       

      TempCalendar:

      LOAD

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

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

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

       

      Calendar:

      Load

                     TempDate AS Date,

                     TempDate as CalcDate,

                     Year(TempDate) As Year,

                     Month(TempDate) As Month,

                     YeartoDate(TempDate)*-1 as CurYTDFlag,

                     YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

                     if(TempDate <= Today(),if(TempDate >= (Today()-365),1,0),0) as RC12,

                     if(TempDate <= Today(),if(TempDate >= (Today()-182),1,0),0) as RC6,

                     date(monthstart(TempDate), 'MM-YYYY') as MonthYear,

                     ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                     MonthName(TempDate) as MonthName

      Resident TempCalendar;

      DROP Table TempCalendar;