4 Replies Latest reply: May 9, 2016 5:47 AM by Shashank Seth RSS

    Master Calendar creation

    Shashank Seth

      Hi Genuises,

       

      I am using the below script to generate the master calendar, earlier it was working fine but now it is giving me the error

       

      Field not found - <<=>

      TempCalendar: 

      LOAD 

                      + Iterno()-1 As Num, 

                    Date( + IterNo() - 1) as TempDate 

                    AutoGenerate 1 While  + IterNo() -1 <=




      Below is the script I am using

       

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

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

      AUTOGENERATE (12); 

       

      Temp: 

      Load 

                    '1/1/2016' as minDate, 

                    '31/12/2020' as maxDate 

      Resident CID; 

       

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

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

      DROP Table Temp; 

       

      TempCalendar: 

      LOAD 

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

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

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

       

      MasterCalendar: 

      Load 

                     Date(TempDate,'M/D/YYYY') AS OrderDate,

                     num(Date(TempDate,'M/D/YYYY')) AS [Created Date Num],

                     num(Date(TempDate,'M/D/YYYY')) AS [Num Master Agreement End Date Calendar], 

                     week(TempDate) As Week, 

                     Year(TempDate) As Year, 

                     Month(TempDate) As Month, 

                     Day(TempDate) As Day, 

                     YeartoDate(TempDate)*-1 as CurYTDFlag, 

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

                     inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

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

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

                     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                     WeekDay(TempDate) as WeekDay 

      Resident TempCalendar 

      Order By TempDate ASC; 

      Drop Table TempCalendar;

       

       

      EXIT Script;

       

       

       

       

      Your help will be deeply appreciated

       

      Thanks in Advance

       

      S

        • Re: Master Calendar creation
          Manish Kachhia

          Try this...

           

          Temp:

          Load

                        Date#('1/1/2016','D/M/YYYY') as minDate,

                        Date#('31/12/2020','DD/MM/YYYY') as maxDate

          Resident CID;

           

          Rest as it is !

          • Re: Master Calendar creation
            Manish Kachhia

            QuartersMap:

            MAPPING LOAD 

            rowno() as Month,

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

            AUTOGENERATE (12);

             

            Temp:

            Load

                          Date#('1/1/2016','D/M/YYYY') as minDate,

                          Date#('31/12/2020','DD/MM/YYYY') as maxDate

            AutoGenerate 1;

             

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

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

            DROP Table Temp;

             

            TempCalendar:

            LOAD

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

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

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

             

            MasterCalendar:

            Load

                           Date(TempDate,'M/D/YYYY') AS OrderDate,

                           num(Date(TempDate,'M/D/YYYY')) AS [Created Date Num],

                           num(Date(TempDate,'M/D/YYYY')) AS [Num Master Agreement End Date Calendar],

                           week(TempDate) As Week,

                           Year(TempDate) As Year,

                           Month(TempDate) As Month,

                           Day(TempDate) As Day,

                           YeartoDate(TempDate)*-1 as CurYTDFlag,

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

                           inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

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

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

                           Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                           WeekDay(TempDate) as WeekDay

            Resident TempCalendar

            Order By TempDate ASC;

            Drop Table TempCalendar;