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.
      FCLLCLQuotation:
      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,

          
      QUTN_POD_9LOCM as POD

      Resident tmpFCLLCLQuotation

      Order By QUTN_UniqID_1UBI;


      TempCalendar:

      Load

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

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

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


      QuoteCalendar:
      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;