Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2014-02-27 07:46 AM

Timestamp difference in Working hours

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_idTraysTxn_DateState_Code
10007344NBG18/09/2013 12:33CMP
10007344NBG18/09/2013 12:33RDY
10007344NSS18/09/2013 16:23RDY
10007344NSS19/09/2013 09:05RUN
10007344NSS19/09/2013 09:07CMP
10007344CJA19/09/2013 09:07RDY
10007344CJA19/09/2013 09:11CMP
10007344NCO19/09/2013 09:11RDY
10007344NCO19/09/2013 09:20RUN
10007344NCO19/09/2013 09:20CMP
10007344NFU25/09/2013 08:28RDY
10007344NFU25/09/2013 08:28RUN
10007344NFU25/09/2013 08:28CMP
10007344NCH25/09/2013 08:28RDY
10007344NCH25/09/2013 11:56RUN
10007344NCH25/09/2013 12:02CMP
10007344NBO25/09/2013 12:02RDY
10007344NBO25/09/2013 12:03RUN
10007344NBO25/09/2013 12:07CMP
10007344NCA25/09/2013 12:07CMP
10007344NCA25/09/2013 12:07RDY

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

Application_idTrayTxn_DateState_Code
10007616NBG12/09/2013 10:42:29CMP
10007616NBG12/09/2013 10:42:29RDY
10007616NSA12/09/2013 10:42:29RDY
10007616NSA12/09/2013 11:25:55RUN
10007616NSA12/09/2013 11:26:33CMP
10007616CJA12/09/2013 11:26:33RDY
10007616CJA12/09/2013 11:27:24CMP
10007616NCO12/09/2013 11:27:24RDY
10007616NCO12/09/2013 12:56:32RUN
10007616NCO12/09/2013 13:00:24CMP
10007616NSA12/09/2013 13:00:24RDY
10007616NSA13/09/2013 16:20:38RUN
10007616NSA13/09/2013 16:22:08CMP
10007616CJA13/09/2013 16:22:08RDY
10007616CJA13/09/2013 16:25:47CMP
10007616NCO13/09/2013 16:25:47RDY
10007616NCO13/09/2013 16:50:57RUN
10007616NCO13/09/2013 16:51:47RDY
10007616NCO16/09/2013 10:42:22RUN
10007616NCO16/09/2013 10:43:00CMP
10007616NFU16/09/2013 10:43:00SUS
10007616NFU24/09/2013 08:31:02RDY
10007616NFU24/09/2013 08:31:02RUN
10007616NFU24/09/2013 08:31:05CMP
10007616NCH24/09/2013 08:31:05RDY
10007616NCH24/09/2013 12:13:58RUN
10007616NCH24/09/2013 12:15:25CMP
10007616NBO24/09/2013 12:15:25RDY
10007616NBO24/09/2013 12:15:26RUN
10007616NBO24/09/2013 12:16:01RDY
10007616NBO24/09/2013 12:16:04RUN
10007616NBO24/09/2013 12:16:05EXP
10007616NCH24/09/2013 12:16:05RDY
10007616NCH24/09/2013 13:25:26RUN
10007616NCH24/09/2013 13:26:20CMP
10007616NRD24/09/2013 13:26:20RDY
10007616NRD24/09/2013 15:30:05RUN
10007616NRD24/09/2013 15:30:07EXP
10007616NCH24/09/2013 15:30:07RDY
10007616NCH24/09/2013 15:30:08RUN
10007616NCH24/09/2013 15:31:42CMP
10007616NBO24/09/2013 15:31:42RDY
10007616NBO24/09/2013 15:31:43RUN
10007616NBO24/09/2013 15:37:25RDY
10007616NBO24/09/2013 15:37:27RUN
10007616NBO24/09/2013 15:37:32EXP
10007616NSA24/09/2013 15:37:32RDY
10007616NSA24/09/2013 15:39:51RUN
10007616NSA24/09/2013 15:41:37CMP
10007616CJA24/09/2013 15:41:37RDY
10007616CJA24/09/2013 15:43:42CMP
10007616NCO24/09/2013 15:43:42RDY
10007616NCO24/09/2013 15:52:46RUN
10007616NCO24/09/2013 15:53:44CMP
10007616NFU24/09/2013 15:53:44SUS
10007616NFU24/09/2013 15:53:47RDY
10007616NFU24/09/2013 15:53:47RUN
10007616NFU24/09/2013 15:54:06CMP
10007616NCH24/09/2013 15:54:06RDY
10007616NCH24/09/2013 15:54:08RUN
10007616NCH24/09/2013 15:54:12CMP
10007616NBO24/09/2013 15:54:12RDY
10007616NBO24/09/2013 15:54:14RUN
10007616NBO24/09/2013 15:55:56CMP
10007616NCA24/09/2013 15:55:56CMP
10007616NCA24/09/2013 15:55:56RDY
800 Views
0 Likes
6 Replies
gopalopsharma
Creator
Creator
‎2014-02-27 07:56 AM

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?

643 Views
0 Likes
Not applicable
‎2014-02-27 08:04 AM
Author

In response to gopalopsharma

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.

643 Views
0 Likes
gopalopsharma
Creator
Creator
‎2014-02-27 09:10 AM

In response to

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

643 Views
0 Likes
Not applicable
‎2014-02-27 09:18 AM
Author

In response to gopalopsharma

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

  1. How can I get answer in working hours (Monday to Friday 9am to 5pm)
  2. 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.
643 Views
0 Likes
gopalopsharma
Creator
Creator
‎2014-02-27 09:35 AM

In response to

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..

643 Views
0 Likes
Not applicable
‎2014-02-27 10:07 AM
Author

In response to gopalopsharma

Thanks Gopal,

  1. 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?
  2. 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?
643 Views
0 Likes