Timestamp difference in Hours

Fri, 14 Feb 2014 10:26:36 GMT

I have the following data in notepad file for each application. Application_Id is unique id.

Process_Code Application_id Workitem_Id Activity_Code Txn_Date State_Code Assigned_User_Id Assigned_Dept_Id Actioned_User_Id

NAP 10003507 79909714 NBG 2013-09-06 10:32:39.900 CMP NULL NULL 99999

NAP 10003507 79909714 NBG 2013-09-06 10:32:39.900 RDY NULL NULL 99999

NAP 10003507 79909715 NSA 2013-09-06 10:32:39.900 RDY NULL NULL 99999

NAP 10003507 79909715 NSA 2013-09-11 09:00:28.380 RUN NULL NULL 12781

NAP 10003507 79909715 NSA 2013-09-11 09:21:09.713 CMP NULL NULL 12781

NAP 10003507 80307667 NSX 2013-09-11 09:21:09.713 RDY NULL NULL 12781

NAP 10003507 80307667 NSX 2013-09-11 09:44:07.147 RUN NULL NULL 29037

NAP 10003507 80307667 NSX 2013-09-11 09:44:10.617 CMP NULL NULL 29037

NAP 10003507 80308416 NSA 2013-09-11 09:44:10.617 RDY NULL NULL 29037

NAP 10003507 80308416 NSA 2013-09-11 09:44:11.787 RUN NULL NULL 29037

NAP 10003507 80308416 NSA 2013-09-11 09:44:29.117 CMP NULL NULL 29037

NAP 10003507 80308424 NSX 2013-09-11 09:44:29.117 RDY NULL NULL 29037

NAP 10003507 80308424 NSX 2013-09-11 09:44:34.600 TRM NULL NULL 29037

NAP 10003507 80403761 NBG 2013-09-17 17:01:54.310 RDY NULL NULL 99999

NAP 10003507 80403761 NBG 2013-09-17 17:01:54.327 CMP NULL NULL 99999

NAP 10003507 80403762 NSA 2013-09-17 17:01:54.327 RDY NULL NULL 99999

NAP 10003507 80403762 NSA 2013-09-19 15:48:37.117 RUN NULL NULL 29037

NAP 10003507 80403762 NSA 2013-09-19 15:57:16.813 CMP NULL NULL 29037

NAP 10003507 80438748 CJA 2013-09-19 15:57:16.813 RDY NULL NULL 29037

NAP 10003507 80438748 CJA 2013-09-19 16:02:13.400 CMP NULL NULL 99999

NAP 10003507 80438829 NCO 2013-09-19 16:02:13.413 RDY NULL NULL 99999

NAP 10003507 80438829 NCO 2013-09-19 16:20:41.390 RUN NULL NULL 29396

NAP 10003507 80438829 NCO 2013-09-19 16:21:07.717 CMP NULL NULL 29396

NAP 10003507 80439054 NFU 2013-09-19 16:21:07.717 SUS NULL NULL 29396

NAP 10003507 80439054 NFU 2013-09-25 08:21:40.923 RDY NULL NULL 88603

NAP 10003507 80439054 NFU 2013-09-25 08:21:41.033 RUN NULL NULL 88603

NAP 10003507 80439054 NFU 2013-09-25 08:21:50.877 CMP NULL NULL 88603

NAP 10003507 80507822 NCH 2013-09-25 08:21:50.893 RDY NULL NULL 88603

NAP 10003507 80507822 NCH 2013-09-25 15:24:55.157 RUN NULL NULL 15095

NAP 10003507 80507822 NCH 2013-09-25 15:24:58.657 RDY NULL NULL 15095

NAP 10003507 80507822 NCH 2013-09-25 15:25:05.563 RUN NULL NULL 15095

NAP 10003507 80507822 NCH 2013-09-25 15:28:54.660 CMP NULL NULL 15095

NAP 10003507 80515611 NBO 2013-09-25 15:28:54.660 RDY NULL NULL 15095

NAP 10003507 80515611 NBO 2013-09-25 15:28:58.067 RUN NULL NULL 15095

NAP 10003507 80515611 NBO 2013-09-25 15:29:02.910 CMP NULL NULL 15095

NAP 10003507 80515614 NCA 2013-09-25 15:29:02.910 CMP NULL NULL 99999

NAP 10003507 80515614 NCA 2013-09-25 15:29:02.910 RDY NULL NULL

Each application passes through different trays but I am only intersted in the following Activity_Code of trays.

Tray	Activity_Code	Application Status
NAPS Contact Customer	NCO	Outbound
NAPS Print Documents	NDO	Outbound
NAPS Follow Up Docs	NFU	Customer
NAPS Returned Documents	NRD	Customer
NAPS Check Documents	NCH	Inbound
NAPS Security Perfection	NSP	Security
NAPS Sec Special Conditions	NSC	Booked
NAPS Booking	NBO	Booked
NAPS Call Off	NCA	Booked

Each tray has different State_Code a below.

State_Code	Code description
RDY	Ready to work
RUN	In process
TRM	Terminated
CMP	Completed

I want to calculate time in hours only workingdays for each application_status if State_code is 'CMP' and application changes its application_status from one to other e.g. application moves from Outbound to Customer trays when State_code is CMP. Workinh hours are start time = 9:00:00 and quit time = 17:00:00.

topic Timestamp difference in Hours in QlikView

Timestamp difference in Hours