How to subtract Begin Time - "Previous" End Time to determine gaps in between phone calls

[#1]

I am trying to create a calculation that will Subtract Begin Time from the previous End Time to determine what the gap is between each call. However, I can't seem to get this to work correctly in QlikView. Any suggestions? Below is a data example. I would need to do this per person, per day.

Name	Date	Begin Time	End Time	Gaps
Joe Smith	9/10/10	7:49:49	7:50:07
Joe Smith	9/10/10	8:09:17	8:13:21
Joe Smith	9/10/10	8:24:47	8:25:15
Joe Smith	9/10/10	8:25:41	8:30:38
Joe Smith	9/10/10	8:31:45	8:32:37
Joe Smith	9/10/10	8:32:59	8:33:29
Joe Smith	9/10/10	8:33:37	8:34:20
Joe Smith	9/10/10	8:34:30	8:39:26
Joe Smith	9/10/10	8:42:51	8:43:58
Joe Smith	9/10/10	8:44:23	8:45:06
Joe Smith	9/10/10	8:45:36	8:49:55
Joe Smith	9/10/10	8:51:27	8:52:28
Joe Smith	9/10/10	9:48:17	9:48:35
Joe Smith	9/10/10	10:43:55	10:45:17

[johnw]

Assuming you want to do it during the load instead of in the chart, and that you're sorting in Name then Date order, maybe this?

if(Name=previous(Name) and Date=previous(Date),interval("Begin Time"-previous("End Time"))) as Gap

[#3]

Thanks for your quick response and example. I used your example and put it in the script as:

if([Lead Dev Rep]=previous([Lead Dev Rep]) and Date=previous(Date),interval([Begin Time]-previous([End Time]))) as Gap,

It is partially working. I would expect to see the dash in the first line since there is not a 'previous' End Time but I get blanks throughout the rest of the column. Is there a way to have it work down the whole column and not have the '-' appear? Thanks for your help.

Lead Dev Rep	Date	Begin Time	End Time	Gap	Nbr of Calls	Duration per Call
					70
Joe Smith	9/9/2010	7:25:36	7:25:50	-	1	0 :00:14
Joe Smith	9/9/2010	7:30:35	7:36:36	0:04:45	1	0 :06:01
Joe Smith	9/9/2010	7:48:02	7:49:38	0:11:26	1	0 :01:36
Joe Smith	9/9/2010	7:51:57	7:55:01	-	1	0 :03:04
Joe Smith	9/9/2010	7:56:05	7:56:10	-	1	0 :00:05
Joe Smith	9/9/2010	7:56:06	7:56:10	0:00:04	1	0 :00:04
Joe Smith	9/9/2010	7:56:21	7:56:27	0:00:11	1	0 :00:06
Joe Smith	9/9/2010	7:56:55	7:57:18	-	1	0 :00:23
Joe Smith	9/9/2010	7:57:53	7:59:00	-	1	0 :01:07
Joe Smith	9/9/2010	7:59:05	7:59:34	0:00:05	1	0 :00:29
Joe Smith	9/9/2010	8:02:41	8:13:27	-	1	0 :10:46
Joe Smith	9/9/2010	8:14:51	8:16:00	0:01:24	1	0 :01:09
Joe Smith	9/9/2010	8:16:44	8:17:25	-	1	0 :00:41
Joe Smith	9/9/2010	8:18:21	8:19:58	0:00:56	1	0 :01:37
Joe Smith	9/9/2010	8:20:41	8:21:23	0:00:43	1	0 :00:42
Joe Smith	9/9/2010	8:22:22	8:23:22	-	1	0 :01:00
Joe Smith	9/9/2010	8:24:27	8:25:38	-	1	0 :01:11
Joe Smith	9/9/2010	8:27:49	8:28:11	0:02:11	1	0 :00:22
Joe Smith	9/9/2010	8:28:22	8:28:45	-	1	0 :00:23
Joe Smith	9/9/2010	8:39:25	8:43:13	-	1	0 :03:48
Joe Smith	9/9/2010	9:03:21	9:06:27	-	1	0 :03:06
Joe Smith	9/9/2010	9:36:34	9:36:47	-	1	0 :00:13

[johnw]

Works fine for me. See attached. Are you "sorting in Name then Date order" like I mentioned? And actually, Begin Time should be there too, so sorting in Name, Date and Begin Time order?

[#5]

That was it. I was only sorting in QV and not in my Extract. I sorted my Excel file first as you mentioned and then loaded it. That worked! Thanks so much!

[johnw]

meganm wrote:That was it. I was only sorting in QV and not in my Extract. I sorted my Excel file first as you mentioned and then loaded it. That worked! Thanks so much!

Well, if it's acceptable to change the Excel file, then I guess that works. Generally speaking, I prefer to not change my source data to conform to QlikView, but rather change QlikView to conform to my source data. So another option, if sorting when reading from Excel doesn't work, would be to read in the file and then do a left join with the gap information:

LEFT JOIN (MyTable)
LOAD
UniqueKeyToMyTable
,if([Lead Dev Rep]=previous([Lead Dev Rep]) and Date=previous(Date),interval([Begin Time]-previous([End Time]))) as Gap
RESIDENT MyTable
ORDER BY [Lead Dev Rep], Date, [Begin Time]
;

See attached. Either way works, of course, so whatever you prefer is good.

[#7]

thanks for the tip. Makes sense