Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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;
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

2 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

Thanks Michael! It worked.