Calendar table creation - issue

Tue, 16 Jul 2013 20:18:49 GMT

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;

Re: Calendar table creation - issue

Anonymous — Tue, 16 Jul 2013 20:23:28 GMT

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

Re: Calendar table creation - issue

Tue, 16 Jul 2013 20:27:38 GMT

Thanks Michael! It worked.

topic Re: Calendar table creation - issue in QlikView

Calendar table creation - issue

Re: Calendar table creation - issue

Re: Calendar table creation - issue