topic Re: Set Analysis Big Challenge in QlikView

Set Analysis Big Challenge

Tue, 30 Dec 2014 17:06:29 GMT

Hi all,

I have a new challenge in my qlikview app but I don't know if it will possible to solve it.

I have data related to customer complaints from my CRM as following example:

Activity_id	Activity_main_id	Created_date	Last_update	Status
1	10	01/11/2014	01/11/2014	Registered
2	10	01/11/2014	10/11/2014	Closed
5	10	01/11/2014	30/11/2014	Re-Opened
6	10	01/11/2014	01/12/2014	Closed
100	10	01/11/2014	03/12/2014	Re-Opened
101	10	01/11/2014	10/12/2014	Closed

My goal is calculate how many complaints have a workflow superior to 72 hours. Now I have calculated all complaints 'closed' that have (Last_update - Created_date) > 72 hours. I have excluded, instead, all activity_main_id that are re-opened because in that case the date difference would be between Last_update(closed) and Last_update(re-opened previous the closed status).

This is a montly KPI, so i expect :

November : 1 complaint because 10/11/2014(activity_id = 2) - 01/11/2014 (date_created) > 72 hh;
December: 1 complaint because [01/12/2014(activity_id = 6) - 30/11/2014 (activity_id = 5) < 72 h] but [10/12/2014 (activity_id= 101) - 03/12/2014(activity_id=100) > 72 h]

So the updated table would be:

Activity_id	Activity_main_id	Created_date	Last_update	Status
1	10	01/11/2014	01/11/2014	Registered
2	10	01/11/2014	10/11/2014	Closed
5	10	30/11/2014	30/11/2014	Re-Opened
6	10	30/11/2014	01/12/2014	Closed
100	10	03/12/2014	03/12/2014	Re-Opened
101	10	03/12/2014	10/12/2014	Closed

In this way I can select only Closed activity_main_id in which Last_update - Created_date > 72 hours.

Any idea it will be appreciated.

Thank you,

Romina

Re: Set Analysis Big Challenge

MK_QSL — Tue, 30 Dec 2014 17:09:32 GMT

What is the final table you are looking for?

Kindly update the requirements also... Thanks

Re: Set Analysis Big Challenge

Tue, 30 Dec 2014 17:22:40 GMT

Hi Manish,

I have added the final table that I need.

Thank you!

Re: Set Analysis Big Challenge

Anonymous — Tue, 30 Dec 2014 17:42:37 GMT

Romina

Would something like this in the load script help ?

Temp :

LOAD

Activity_id,

Activity_main_id,

Created_date,

Last_update,

Status

FROM

[http://community.qlik.com/thread/147048]

(html, codepage is 1252, embedded labels, table is @1);

Data :

NoConcatenate

load

Activity_id,

Activity_main_id,

if ( Status = 'Registered' , Created_date ,

if ( Status = 'Re-Opened' , Last_update , peek( Created_date ) ) ) as Created_date ,

Last_update,

Status

resident Temp

order by Activity_main_id, Activity_id

;

drop table Temp ;