4 Replies Latest reply: Sep 22, 2018 12:56 AM by Syahfira Zakaria RSS

    Master Calendar - Field 'a' not found Error

    Syahfira Zakaria

      Hi. I need help in this so much. This is the master calendar script. I got an error when I loaded the script. It says 'Field 'a' not found'.


      Does anyone sees what's wrong with the script?


      Thanks so much



      QuarterNAME:

       

      LOAD * Inline [

      Quarter , QuarterFullName

      Q1 ,FIRST

      Q2 ,SECOND

      Q3 ,THIRD

      Q4 ,FOURTH

      ];

       

       

      QuartersMap: 

          MAPPING LOAD  

          rowno() as Month, 

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

         

          AUTOGENERATE (12); 


      MaxMinDates:

      Load

      Num(Min([Reporting Date])) as MinDate,

      Num(Max([Reporting Date])) as MaxDate

      Resident [Timesheet];


           

      varMinDate = Peek('MinDate', -1, 'MaxMinDates');

      varMaxDate = Peek('MaxDate', -1, 'MaxMinDates');

              

        

      TempCalendar: 

          LOAD 

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

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

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

        


      TRACE VARMAXDATE: $(varMaxDate);


      ManhourMasterCalendar: 


      LOAD

      *;

       

      Load 

                     

      trim(date(TempDate,'DD/MM/YYYY')) as [Reporting Date],

          day(TempDate) as [Reporting Day],

          date(TempDate,'WWWW') as [Reporting Full Day Name],

          year(TempDate) as [Reporting Year],

          inyear(TempDate, date('$(varMaxDate)'),0) * -1  as [Reporting CY],  

          inyear(TempDate, date('$(varMaxDate)'),-1) * -1 as [Reporting First PY],

          inyear(TempDate, date('$(varMaxDate)'),-2) * -1 as [Reporting Second PY],

          inyeartodate(TempDate, date('$(varMaxDate)'),0) * -1  as [Reporting CYTD],

          inyeartodate(TempDate, date('$(varMaxDate)'),-1) * -1 as [Reporting First PYTD],

          inyeartodate(TempDate, date('$(varMaxDate)'),-2) * -1 as [Reporting Second PYTD],

          InMonth(TempDate, date('$(varMaxDate)'),0) * -1 as [Reporting CMTD],


         

          ApplyMap('QuartersMap', month(TempDate), Null()) as [Reporting Quarter],

         

          quarterName(TempDate) as [Reporting Quarter Name],

          yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)  as [Reporting Quarter Year],

         

          inquarter(TempDate, date('$(varMaxDate)'),0) * -1 as [Reporting CQ], 

          inquarter(TempDate, date('$(varMaxDate)'),-4) * -1 as [Reporting First PQ],

          inquarter(TempDate, date('$(varMaxDate)'),-8) * -1 as [Reporting Second PQ], 

          date(monthstart(TempDate),'MM') as [Reporting Month Number],

          month(TempDate) as [Reporting Month],

          date(monthstart(TempDate),'MMMM') as [Reporting Month Full Name],

          monthstart(TempDate) as [Reporting Calendar Month Start Date],

          monthend(TempDate) as [Reporting Calendar Month End Date],

      date(monthstart(TempDate), 'MMM-YYYY') as [Reporting Month Year],


          week(TempDate) as [Reporting Week],

          week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Reporting Week Year],

          week(weekstart(TempDate)) & '-' & Month(TempDate)    as [Reporting Week Month],

          if(weekday(TempDate)<5, 'WEEKDAY', 'WEEKEND') as [Reporting Weekday or Weekend]


      Resident TempCalendar 

      Order By TempDate ASC; 


      Drop Table TempCalendar;

      Drop Table  QuarterNAME;

      Drop Table  MaxMinDates;