topic Re: Timestamp difference in Working hours in QlikView

Timestamp difference in Working hours

Thu, 27 Feb 2014 12:46:16 GMT

Application can move in different trays in the workflow. Each tray has different State_Codes. e.g. Tray = NSS has State_code RDY, RUN & CMP in the following example. But each tray would have RDY (shows application came in a tray) and CMP (application completed in a tray). How can I get that how many working hours each application stayed in each tray. Working days are Monday to Friday and working hours are 9:00-17:00. There is also a problem that the application can go in each tray once or more than once as two examples below.

Once example: In this example application moved in each tray only once.

Application_id	Trays	Txn_Date	State_Code
10007344	NBG	18/09/2013 12:33	CMP
10007344	NBG	18/09/2013 12:33	RDY
10007344	NSS	18/09/2013 16:23	RDY
10007344	NSS	19/09/2013 09:05	RUN
10007344	NSS	19/09/2013 09:07	CMP
10007344	CJA	19/09/2013 09:07	RDY
10007344	CJA	19/09/2013 09:11	CMP
10007344	NCO	19/09/2013 09:11	RDY
10007344	NCO	19/09/2013 09:20	RUN
10007344	NCO	19/09/2013 09:20	CMP
10007344	NFU	25/09/2013 08:28	RDY
10007344	NFU	25/09/2013 08:28	RUN
10007344	NFU	25/09/2013 08:28	CMP
10007344	NCH	25/09/2013 08:28	RDY
10007344	NCH	25/09/2013 11:56	RUN
10007344	NCH	25/09/2013 12:02	CMP
10007344	NBO	25/09/2013 12:02	RDY
10007344	NBO	25/09/2013 12:03	RUN
10007344	NBO	25/09/2013 12:07	CMP
10007344	NCA	25/09/2013 12:07	CMP
10007344	NCA	25/09/2013 12:07	RDY

More than Once example: Application moved into NSA twice, CJA three times etc.

Application_id	Tray	Txn_Date	State_Code
10007616	NBG	12/09/2013 10:42:29	CMP
10007616	NBG	12/09/2013 10:42:29	RDY
10007616	NSA	12/09/2013 10:42:29	RDY
10007616	NSA	12/09/2013 11:25:55	RUN
10007616	NSA	12/09/2013 11:26:33	CMP
10007616	CJA	12/09/2013 11:26:33	RDY
10007616	CJA	12/09/2013 11:27:24	CMP
10007616	NCO	12/09/2013 11:27:24	RDY
10007616	NCO	12/09/2013 12:56:32	RUN
10007616	NCO	12/09/2013 13:00:24	CMP
10007616	NSA	12/09/2013 13:00:24	RDY
10007616	NSA	13/09/2013 16:20:38	RUN
10007616	NSA	13/09/2013 16:22:08	CMP
10007616	CJA	13/09/2013 16:22:08	RDY
10007616	CJA	13/09/2013 16:25:47	CMP
10007616	NCO	13/09/2013 16:25:47	RDY
10007616	NCO	13/09/2013 16:50:57	RUN
10007616	NCO	13/09/2013 16:51:47	RDY
10007616	NCO	16/09/2013 10:42:22	RUN
10007616	NCO	16/09/2013 10:43:00	CMP
10007616	NFU	16/09/2013 10:43:00	SUS
10007616	NFU	24/09/2013 08:31:02	RDY
10007616	NFU	24/09/2013 08:31:02	RUN
10007616	NFU	24/09/2013 08:31:05	CMP
10007616	NCH	24/09/2013 08:31:05	RDY
10007616	NCH	24/09/2013 12:13:58	RUN
10007616	NCH	24/09/2013 12:15:25	CMP
10007616	NBO	24/09/2013 12:15:25	RDY
10007616	NBO	24/09/2013 12:15:26	RUN
10007616	NBO	24/09/2013 12:16:01	RDY
10007616	NBO	24/09/2013 12:16:04	RUN
10007616	NBO	24/09/2013 12:16:05	EXP
10007616	NCH	24/09/2013 12:16:05	RDY
10007616	NCH	24/09/2013 13:25:26	RUN
10007616	NCH	24/09/2013 13:26:20	CMP
10007616	NRD	24/09/2013 13:26:20	RDY
10007616	NRD	24/09/2013 15:30:05	RUN
10007616	NRD	24/09/2013 15:30:07	EXP
10007616	NCH	24/09/2013 15:30:07	RDY
10007616	NCH	24/09/2013 15:30:08	RUN
10007616	NCH	24/09/2013 15:31:42	CMP
10007616	NBO	24/09/2013 15:31:42	RDY
10007616	NBO	24/09/2013 15:31:43	RUN
10007616	NBO	24/09/2013 15:37:25	RDY
10007616	NBO	24/09/2013 15:37:27	RUN
10007616	NBO	24/09/2013 15:37:32	EXP
10007616	NSA	24/09/2013 15:37:32	RDY
10007616	NSA	24/09/2013 15:39:51	RUN
10007616	NSA	24/09/2013 15:41:37	CMP
10007616	CJA	24/09/2013 15:41:37	RDY
10007616	CJA	24/09/2013 15:43:42	CMP
10007616	NCO	24/09/2013 15:43:42	RDY
10007616	NCO	24/09/2013 15:52:46	RUN
10007616	NCO	24/09/2013 15:53:44	CMP
10007616	NFU	24/09/2013 15:53:44	SUS
10007616	NFU	24/09/2013 15:53:47	RDY
10007616	NFU	24/09/2013 15:53:47	RUN
10007616	NFU	24/09/2013 15:54:06	CMP
10007616	NCH	24/09/2013 15:54:06	RDY
10007616	NCH	24/09/2013 15:54:08	RUN
10007616	NCH	24/09/2013 15:54:12	CMP
10007616	NBO	24/09/2013 15:54:12	RDY
10007616	NBO	24/09/2013 15:54:14	RUN
10007616	NBO	24/09/2013 15:55:56	CMP
10007616	NCA	24/09/2013 15:55:56	CMP
10007616	NCA	24/09/2013 15:55:56	RDY

Re: Working hours difference

gopalopsharma — Thu, 27 Feb 2014 12:56:46 GMT

Hello Shoaib,

Can you explain how will you calculate the working hours? (I mean formula/ Logic)

also what are the distinct values in State_code and what do they signify?

Re: Timestamp difference in Working hours

Thu, 27 Feb 2014 13:04:08 GMT

Hi Gopal,

I dont have any formula but the logic is that if application moves in a tray then the state_code would be RDY and when application leaves the tray then state_code would be CMP. So the timestamp difference of a tray between RDY and CMP. If application goes into a tray more than once then add those difference of a tray into one. State_code are RDY (application came), RUN (In progress), TRM (Terminated), EXP (Exceptional), SUS (Suspended) and CMP (Completed). Most of the applications have RDY, RUN and CMP.

Re: Timestamp difference in Working hours

gopalopsharma — Thu, 27 Feb 2014 14:10:25 GMT

Hello Shoaib,

Then I would prefer to do it in script.

Load the same file, create two additional fileds like the code below:

Load *,

if( State_code = 'rdy',time,0) as StartTime,

if(State_code= 'cmp', time, 0) as EndTime

From <filename>;

Now Our answer is simple EndTime-StartTime in any chart. or If you wish you can create a field for doing the subtraction. I hope you understand the logic.

One thing to be very conscious that Time field should be in proper Date format else the Subtraction won't work.

Hope this helps

Re: Timestamp difference in Working hours

Thu, 27 Feb 2014 14:18:21 GMT

Thanks Gopal but it is not too simple. There are some issues as below:

How can I get answer in working hours (Monday to Friday 9am to 5pm)
Application can go in a same tray multiple time then how will it work. e.g. how will add first time RDY and CMP difference and second time RDY and CMP difference and so on.

Re: Timestamp difference in Working hours

gopalopsharma — Thu, 27 Feb 2014 14:35:47 GMT

Hello Rafiq,

Reponses Inline

1. I hope here you do not want to calculate for Saturday and Sunday. Then do the following:

After loading the table above, use a resident load on it.

Load *,

Hour(if(Weekday(time)<>'Sat' or Weekday(Time)<>'Sun', StartTime-EndTime,0)) as Timediff

Resident <tablename>;

All in all we have 2 functions in QV- hour and Weekday.

2. I also felt stuck about this question before I replied to your post. One thing to notice here is that we are not doing calculation on StartTime or EndTime we will be summing up Timediff Field, hence this will be avoided. In chart we will use Sum(TimeDiff) as expression.

Hope this helps..

Re: Timestamp difference in Working hours

Thu, 27 Feb 2014 15:07:14 GMT

Thanks Gopal,

Yes I dont want to calculate Saturday and Sunday. Also I want to calculate only between 9am to 5pm. How can I restrict time 9 am to 5pm?
So you are saying that if application moves in a tray multiples times then it will add up. e.g application moved in a tray three times then the code will add three differences into one?