Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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
What is the final table you are looking for?
Kindly update the requirements also... Thanks
Hi Manish,
I have added the final table that I need.
Thank you!
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 ;