Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis Big Challenge

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_idActivity_main_idCreated_dateLast_updateStatus
11001/11/201401/11/2014Registered
21001/11/201410/11/2014Closed
51001/11/201430/11/2014Re-Opened
61001/11/201401/12/2014Closed
1001001/11/201403/12/2014Re-Opened
1011001/11/201410/12/2014Closed

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_idActivity_main_idCreated_dateLast_updateStatus
11001/11/201401/11/2014Registered
21001/11/201410/11/2014Closed
51030/11/201430/11/2014Re-Opened
61030/11/201401/12/2014Closed
1001003/12/201403/12/2014Re-Opened
1011003/12/201410/12/2014Closed

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

Tags (3)
3 Replies

Re: Set Analysis Big Challenge

What is the final table you are looking for?

Kindly update the requirements also... Thanks

Not applicable

Re: Set Analysis Big Challenge

Hi Manish,

I have added the final table that I need.

Thank you!

Re: Set Analysis Big Challenge

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 ;

Community Browser