9 Replies Latest reply: Feb 27, 2012 5:00 AM by Mohammed Mukram RSS

    Calendar

    chris goudy

      I'm having trouble getting a calendar to work in my model.

       

      I have the script for making a calendar from a previous model, but I think there are some changes I need to make to the scrpt that I am overlooking.

       

      Here is the script I have for the calendar:

       

      //*************** Get Dates from Data ***************

      DateTemp:

      LOAD Date(Date#(Timestamp,'YYYYMMDD'),'DD/MM/YYYY') as Timestamp

      RESIDENT CallLogs

      ORDER BY Timestamp Asc;

       

       

      LET vMinDate = Num(Peek('Timestamp', 0, 'DateTemp'));  //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable

      LET vMaxDate = Num(Peek('Timestamp', -1, 'DateTemp'));    

      LET vToday = Num(today());

       

       

      //*************** Temporary Calendar ***************

      TempCalendar:

      LOAD

      $(vMinDate)+IterNo()-1 AS Num,

      Date($(vMinDate)+IterNo()-1) AS TempDate

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

       

       

       

       

      //*************** Master Calendar ***************

      MasterCalendar:

      LEFT KEEP (CallLog)

      LOAD TempDate AS %_DateKey,

        TempDate as Date,

           week(TempDate) AS Week,

           year(TempDate) AS Year,

           month(TempDate) AS Month,

           day(TempDate) AS Day,

           weekday(TempDate) AS WeekDay,

           'Q' & ceil(month(TempDate)/3) as Quarter,

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

           week(TempDate)&'-'&Year(TempDate) AS WeekYear,

           Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

           Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

           num(month(TempDate)) as Period

      RESIDENT TempCalendar 

      ORDER BY TempDate Asc;

       

       

      Drop table TempCalendar;

      Drop table DateTemp;

        • Calendar
          Celambarasan Adhimulam

          Hi,

               I think you have to change this TempDate as Date to TempDate as Timestamp then you can able to join.


          MasterCalendar:

          LEFT KEEP (CallLog)

          LOAD TempDate AS %_DateKey,

            TempDate as Date,

               week(TempDate) AS Week,

               year(TempDate) AS Year,

               month(TempDate) AS Month,

               day(TempDate) AS Day,

               weekday(TempDate) AS WeekDay,

               'Q' & ceil(month(TempDate)/3) as Quarter,

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

               week(TempDate)&'-'&Year(TempDate) AS WeekYear,

               Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

               Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

               num(month(TempDate)) as Period

          RESIDENT TempCalendar

          ORDER BY TempDate Asc;

           

          Celambarasan

            • Calendar
              chris goudy

              i changed

               

              TempDate as Date

               

              to

               

              TempDate as Timestamp

               

              BUT i still get the following error messages when Reloading:

               

               

              Field not found

              DateTemp:

              LOAD Date(Date#(Timestamp,'YYYYMMDD'),'DD/MM/YYYY') as Timestamp

              RESIDENT CallLogs

              ORDER BY Timestamp Asc

               

               

              Field not found - <<=>

              TempCalendar:

              LOAD

              +IterNo()-1 AS Num,

              Date(+IterNo()-1) AS TempDate

              AUTOGENERATE 1 WHILE (+IterNo()-1) <=

               

               

              Table not found

              MasterCalendar:

              LEFT KEEP (CallLog)

              LOAD TempDate AS %_DateKey,

                TempDate as Timestamp,

                   week(TempDate) AS Week,

                   year(TempDate) AS Year,

                   month(TempDate) AS Month,

                   day(TempDate) AS Day,

                   weekday(TempDate) AS WeekDay,

                   'Q' & ceil(month(TempDate)/3) as Quarter,

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

                   week(TempDate)&'-'&Year(TempDate) AS WeekYear,

                   Year2Date(TempDate, 0, 1, 40966)*-1 AS CurYTDFlag,

                   Year2Date(TempDate,-1, 1, 40966)*-1 AS LastYTDFlag,

                   num(month(TempDate)) as Period

              RESIDENT TempCalendar 

              ORDER BY TempDate Asc

               

               

              Table not found

              DROP TABLES statement

               

               

              Table not found

              DROP TABLES statement