Calendar table creation - issue

[#1]

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;

[Anonymous · Accepted solution]

I suspect that the QuoteDate in the FCLLCLQuotation table is a timestamp rather than date, only represented as integer date - hence nothing is linked.  To fix, change to:

date(floor(QUTN_QtsDateTime_0NDT)) as QuoteDate

[#3]

Thanks Michael! It worked.