Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've tried to create a canonical calendar for my qlikview project using sample script form forum community but it's seems not working fine. The data displayed is not same as my uploaded data.
Data not display in column field 'ClosedDate' and 'CalendarDate'. Supposedly, the data in 'Closed date' field must be exactly same as 'Closed on' field.
Please help.
Thanks.
Hi,
The problem is not with your calendar script but with the way you are creating ClosedDate & CreatedDate.
Try
Date(Floor(Timestamp#(Left([Created On],len([Created On])-3), 'MM/DD/YYYY hh:mm TT')))
Anyway, FYI below is a better calendar script
Fact:
NoConcatenate
LOAD
[Ticket Number],
Category,
[Closed On],
[Created On],
Date(Floor(Timestamp#(Left([Created On],len([Created On])-3), 'MM/DD/YYYY hh:mm TT'))) as Created_Date,
Date(Floor(Timestamp#(Left([Closed On],len([Closed On])-3), 'MM/DD/YYYY hh:mm TT'))) as Closed_Date,
[Due On],
Priority,
Status,
[Time Spent],
[Time To Resolve],
Impact,
Type,
Department,
Source,
[Problem Resolution],
Severity
Resident Ticket;
Drop Table Ticket;
DateBridge:
Load
[Ticket Number],
Created_Date as CalendarDate,
'Created' as DateType
Resident Fact;
Load
[Ticket Number],
Closed_Date as CalendarDate,
'Closed' as DateType
Resident Fact
;
Calendar_Created_Date:
NoConcatenate
LOAD
Date(Calendar_Date, 'MM/DD/YYYY') as CalendarDate,
Year(Calendar_Date) as [Calendar Year],
'Q' & Ceil(Month(Calendar_Date) / 3) as [Calendar Quarter],
Month(Calendar_Date) as [Calendar Month],
Day(Calendar_Date) as [Calendar Day],
Week(Calendar_Date) as [Calendar Week];
Load Date(MinDate + IterNo() -1 ) as Calendar_Date
While
(MinDate + IterNo() - 1) <= Num(MaxDate);
LOAD
min(FieldValue('CalendarDate', recno()))-1 as MinDate,
max(FieldValue('CalendarDate', recno())) as MaxDate
AUTOGENERATE FieldValueCount('CalendarDate');
Hi,
The problem is not with your calendar script but with the way you are creating ClosedDate & CreatedDate.
Try
Date(Floor(Timestamp#(Left([Created On],len([Created On])-3), 'MM/DD/YYYY hh:mm TT')))
Anyway, FYI below is a better calendar script
Fact:
NoConcatenate
LOAD
[Ticket Number],
Category,
[Closed On],
[Created On],
Date(Floor(Timestamp#(Left([Created On],len([Created On])-3), 'MM/DD/YYYY hh:mm TT'))) as Created_Date,
Date(Floor(Timestamp#(Left([Closed On],len([Closed On])-3), 'MM/DD/YYYY hh:mm TT'))) as Closed_Date,
[Due On],
Priority,
Status,
[Time Spent],
[Time To Resolve],
Impact,
Type,
Department,
Source,
[Problem Resolution],
Severity
Resident Ticket;
Drop Table Ticket;
DateBridge:
Load
[Ticket Number],
Created_Date as CalendarDate,
'Created' as DateType
Resident Fact;
Load
[Ticket Number],
Closed_Date as CalendarDate,
'Closed' as DateType
Resident Fact
;
Calendar_Created_Date:
NoConcatenate
LOAD
Date(Calendar_Date, 'MM/DD/YYYY') as CalendarDate,
Year(Calendar_Date) as [Calendar Year],
'Q' & Ceil(Month(Calendar_Date) / 3) as [Calendar Quarter],
Month(Calendar_Date) as [Calendar Month],
Day(Calendar_Date) as [Calendar Day],
Week(Calendar_Date) as [Calendar Week];
Load Date(MinDate + IterNo() -1 ) as Calendar_Date
While
(MinDate + IterNo() - 1) <= Num(MaxDate);
LOAD
min(FieldValue('CalendarDate', recno()))-1 as MinDate,
max(FieldValue('CalendarDate', recno())) as MaxDate
AUTOGENERATE FieldValueCount('CalendarDate');
Hi Lily,
I see the [Closed On] column contains timestamp. So can you try using the below expression in the script.
Date(Floor(Timestamp#([Closed On],'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as [Closed date],
I would have tested it my self but I do not the the excel file.
cheers,
Basav
Hi @basav
FYI
If you notice the timestamp contains timezone "UTC". Your expression does not address that. As @Lily shared the QVW you can do a binary load and use it to play around.
Binary [test calendar.qvw];
Drop Tables DateBridge, Calendar;
New_Ticket:
NoConcatenate
Load * Resident Ticket;
Drop Table Ticket;
Hi @vamsee,
I'm glad with your solution. Now I understand why my calendar is not working. You're right, it is because of my Created and Closed Date script is not in the correct format.
And thanks again for the calendar script given.
Hope, this script will help others too.
Hi @basav ,
Thanks for effort to help me.