Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Timestamp in hours

I have the following data in notepad file.


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.

TrayActivity_CodeApplication Status
NAPS Contact CustomerNCOOutbound
NAPS Print DocumentsNDOOutbound
NAPS Follow Up DocsNFUCustomer
NAPS Returned DocumentsNRDCustomer
NAPS Check DocumentsNCHInbound
NAPS Security PerfectionNSPSecurity
NAPS Sec Special ConditionsNSCBooked
NAPS BookingNBOBooked
NAPS Call OffNCABooked

Each tray has different State_Code a below.

State_CodeCode description
RDYReady to work
RUNIn process
TRMTerminated
CMPCompleted

I want to calculate time in hours only workingdays in each application_status when State_code is 'CMP' when application changes its application_status from one to other e.g. from outbound to customer. start time = 9:00:00 and quit time = 17:00:00.

0 Replies