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: 
Lily
Contributor III
Contributor III

Master Calendar/Canonical Calendar - Problem with data

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. 

QV calendar.PNG

1 Solution

Accepted Solutions
vamsee
Specialist
Specialist

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');

 

 

vamsee_0-1593469101800.png

 

 

View solution in original post

5 Replies
vamsee
Specialist
Specialist

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');

 

 

vamsee_0-1593469101800.png

 

 

basav
Creator
Creator

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

vamsee
Specialist
Specialist

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;
Lily
Contributor III
Contributor III
Author

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. 

 

Lily
Contributor III
Contributor III
Author

Hi @basav , 

Thanks for effort to help me.