Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I am a new user of Qlikview and have encountered the following issue to solve:
I have helpdesk data of the following structure:
Final:
Case id | Case_Create_date | Case_Close_date | Case status | Task id | Task_Create_date | Task_Close_date | Task status
1 | 07/10/2014 | | Open | 1 | 07/10/2014 | | Open
1 | 07/10/2014 | | Open | 1 | 07/10/2014 | 08/10/2014 | Closed
1 | 07/10/2014 | | Open | 2 | 08/10/2014 | | Open
1 | 07/10/2014 | 08/10/2014 | Closed | 2 | 08/10/2014 | 08/10/2014 | Closed
2 | 07/10/2014 | | Open | 3 | 07/10/2014 | | Open
2 | 07/10/2014 | 08/10/2014 | Closed | 3 | 07/10/2014 | 08/10/2014 | Closed
3 | 07/10/2014 | | Open | 4 | 08/10/2014 | | Open
3 | 07/10/2014 | | Open | 4 | 08/10/2014 | 08/10/2014 | Closed
3 | 07/10/2014 | | Open | 5 | 08/10/2014 | | Open
3 | 07/10/2014 | 08/10/2014 | Closed | 5 | 08/10/2014 | 08/10/2014 | Closed
The problem is that I would like to create a master calendar taking into account all date fields (Case Open, Case Close, Task Open, Task Close Dates), as there is a need to show historical changes of Cases/ Tasks.
I tried to use the blog posts: @http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date
and Linking to two or more dates.
Using the logic of these blogs, I have created a Cases table and a Tasks table using the following code:
Cases:
LOAD
distinct Case id ,
Case_Create_date as CaseDate,
1 as CaseOpenCount
RESIDENT Final
;
CONCATENATE (Cases) LOAD
distinct Case id,
Case_Close_date as CaseDate,
1 as CaseCloseCount
RESIDENT Final
where len(Case_Close_date)>1 // Take the final row of Case id with not null Case_Close_date
;
Tasks:
LOAD
Case id,
Task_Create_date as TaskDate,
1 as TaskOpenCount
RESIDENT Final
;
CONCATENATE (Tasks) LOAD
Case id,
Task_Close_date as TaskDate,
1 as TaskCloseCount
RESIDENT Final
where len(Task_Close_date)>1 // Take the row of Case id with not null Task_Close_date
;
The reason I created these tables is that I would like to create the DateLink table based on the common Date (canonical Date).
LOAD
Case id // Fact Key
,CaseDate as Date
,'Cases' as DateType // Fact Type
RESIDENT Cases // from Orders
LOAD // Repeat for Tasks
Case id
,TaskDate as Date
,'Tasks' as DateType
RESIDENT Tasks@
The problem is that the canonical date cannot be created...
Is there any thought/ suggestion?
"The problem is that the canonical date cannot be created..."
Could you describe what´s happening?
do you get script errors?
One of the issues could be the missing brackets round the field name "Case id".
It should be [Case id] instead.
hope this helps
regards
Marco
Hi,
The easy one: I do not get script errors.
The other one: "Could you describe what´s happening?" will try to put it in simple and clear words:
The main user of the application would like to see how many Cases and Tasks open and close every day, every hour - see the whole history of these dimensions. This will be shown using barcharts - find example below:
The Case id 687151 opened on the 17/9/2014, had 1 task (Task id 1590734) which opened on the 17/09/2014 and both closed on the 18/9/2014.
In the above graphs we can see the history of each dimension - Case and Task. But the dates used for the graphs are different (CaseDate and TaskDate).
If I try to create the DateLink table with CaseDate and TaskDate as Date (canonical date), in the graphs there are duplicates destroying the whole history of events.
Hope this is more clear. Thank you for your time!
Well,
This attached qvw has a similar requirement.
Please check if fits your needs
Thank you Clever Anjos for the try and the idea, but it did not help alot...
The issue is that we would like the "Task" dimension to be hanged under the "Case" dimension.
In more detail:
Open Case at 1st October and Closed at 9th October.
Within these 9 days that the Case was open, 4 different Tasks were opened and closed.
Under the Case period to be able to see the evolution of Tasks (Open and Close) in a chart (evolution of time)