topic Count Department Hours in QlikView

Count Department Hours

detmawin — Sat, 26 Aug 2017 22:46:52 GMT

MC EMERGENCY	11/22/2016 3:10:00 PM	11/22/2016 6:03:00 PM
MC EMERGENCY	11/22/2016 6:03:00 PM	11/22/2016 6:07:00 PM
MC 6W CV PCU	11/22/2016 6:07:00 PM	11/23/2016 12:10:00 PM
MC CARDIAC CATH LAB	11/23/2016 12:10:00 PM	11/23/2016 3:23:00 PM
MC 6W CV PCU	11/23/2016 3:23:00 PM	11/24/2016 9:49:00 AM
MC 6W CV PCU	11/24/2016 9:49:00 AM	11/28/2016 6:05:00 AM
MC CV OR PERIOP	11/28/2016 6:05:00 AM	11/28/2016 6:33:00 AM
MC 3E PREOP/PHASE II	11/28/2016 6:33:00 AM	11/28/2016 7:23:00 AM
MC OR	11/28/2016 7:23:00 AM	11/28/2016 11:27:00 AM
MC CVICU	11/28/2016 11:27:00 AM	12/2/2016 12:20:00 PM
MC 6W CV PCU	12/2/2016 12:20:00 PM	12/6/2016 7:46:00 AM
MC CV OR PERIOP	12/6/2016 7:46:00 AM	12/6/2016 7:50:00 AM
MC 3E PREOP/PHASE II	12/6/2016 7:50:00 AM	12/6/2016 8:34:00 AM
MC OR	12/6/2016 8:34:00 AM	12/6/2016 10:49:00 AM
MC CVICU	12/6/2016 10:49:00 AM	12/14/2016 11:44:00 AM
MC CV OR PERIOP	12/14/2016 11:44:00 AM	12/14/2016 11:59:00 AM
MC CVICU	12/14/2016 11:59:00 AM	12/27/2016 12:51:00 PM
MC OR PERIOP	12/27/2016 12:51:00 PM	12/27/2016 1:38:00 PM
MC CVICU	12/27/2016 1:38:00 PM	1/4/2017 5:13:00 PM

Good day,

I am trying to count the time interval for department hours for the areas in green. If the department is *ICU, count the time inteval if the department is "MC 6W CV PCU"

do not count any departments until *ICU again and not if it falls between "MC 6W CV PCU" and ICU. Do you know how I can achieve this?

Thanks.

Re: Count Department Hours

vinieme12 — Sun, 27 Aug 2017 05:39:22 GMT

can you also post the expected output?

Re: Count Department Hours

antoniotiman — Sun, 27 Aug 2017 08:25:00 GMT

Hi,

maybe like this

LOAD *,If(Flag=1,Time(DateEnd-DateStart)) as Interval; // If You Want Interval in Script
LOAD *,RowNo() as Rowno,
If(WildMatch(Type,'*ICU'),1,If(Type='MC 6W CV PCU',0,Peek(Flag))) as Flag Inline [
Type,DateStart,DateEnd
MC EMERGENCY, 11/22/2016 3:10:00 PM, 11/22/2016 6:03:00 PM
MC EMERGENCY, 11/22/2016 6:03:00 PM, 11/22/2016 6:07:00 PM
MC 6W CV PCU, 11/22/2016 6:07:00 PM, 11/23/2016 12:10:00 PM
MC CARDIAC CATH LAB, 11/23/2016 12:10:00 PM, 11/23/2016 3:23:00 PM
MC 6W CV PCU, 11/23/2016 3:23:00 PM, 11/24/2016 9:49:00 AM
MC 6W CV PCU, 11/24/2016 9:49:00 AM, 11/28/2016 6:05:00 AM
MC CV OR PERIOP, 11/28/2016 6:05:00 AM, 11/28/2016 6:33:00 AM
MC 3E PREOP/PHASE II, 11/28/2016 6:33:00 AM, 11/28/2016 7:23:00 AM
MC OR, 11/28/2016 7:23:00 AM, 11/28/2016 11:27:00 AM
MC CVICU, 11/28/2016 11:27:00 AM, 12/2/2016 12:20:00 PM
MC 6W CV PCU, 12/2/2016 12:20:00 PM, 12/6/2016 7:46:00 AM
MC CV OR PERIOP, 12/6/2016 7:46:00 AM, 12/6/2016 7:50:00 AM
MC 3E PREOP/PHASE II, 12/6/2016 7:50:00 AM, 12/6/2016 8:34:00 AM
MC OR, 12/6/2016 8:34:00 AM, 12/6/2016 10:49:00 AM
MC CVICU, 12/6/2016 10:49:00 AM, 12/14/2016 11:44:00 AM
MC CV OR PERIOP, 12/14/2016 11:44:00 AM, 12/14/2016 11:59:00 AM
MC CVICU, 12/14/2016 11:59:00 AM, 12/27/2016 12:51:00 PM
MC OR PERIOP, 12/27/2016 12:51:00 PM, 12/27/2016 1:38:00 PM
MC CVICU, 12/27/2016 1:38:00 PM, 1/4/2017 5:13:00 PM
];

Regards,

Antonio

Re: Count Department Hours

detmawin — Sun, 27 Aug 2017 21:25:25 GMT

Thanks very much, I ended up using just peeking on the missing flag fields, but this works as well.