Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

3 Replies
Highlighted
MVP
MVP

What is the final table you are looking for?

Kindly update the requirements also... Thanks

Highlighted
Not applicable

Hi Manish,

I have added the final table that I need.

Thank you!

Highlighted

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 ;