2 Replies Latest reply: Jul 16, 2013 4:27 PM by Kiran Dsouza RSS

    Calendar table creation - issue

      Ive tried using below code to have a calender based navigation on quote data. However the data dont get associated as intended. I get "-" values in the calendar fileds as shown in attachment.
      Appreciate any help on this one.
      LOAD QUTN_UniqID_1UBI as QuoteID,

      QUTN_RefNo_0HVC as QuoteIDDesc,

      date(num(QUTN_QtsDateTime_0NDT,'0')) as QuoteDate,

           QUTN_CustId_9CUST as CustomerID,

      QUTN_QuoteType_0HCH as QuoteType,

      QUTN_FreightType_0HCH as ServiceType,

      QUTN_CreatedBy_0HVC as UserID,

      QUTN_CreatedOn_0NDT as RecTimeStamp,

      QUTN_POR_9LOCM as POR,


      Resident tmpFCLLCLQuotation

      Order By QUTN_UniqID_1UBI;



      $(varMinDate) + RowNo() -1 AS Num,

      Date($(varMinDate) + RowNo() -1) AS TempDate

      AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

      Load TempDate as QuoteDate,

      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,

      InYearToDate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,

      InYearToDate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag

      RESIDENT TempCalendar

      Order by TempDate ASC;

      Drop Table TempCalendar;