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;