Canonical date issue

[Anonymous]

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?

[Clever_Anjos]

"The problem is that the canonical date cannot be created..."

Could you describe what´s happening?

[MarcoWedel]

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

[Anonymous]

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!

[Clever_Anjos]

Well,

This attached qvw has a similar requirement.

Please check if fits your needs

[Anonymous]

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)