topic Calculating Employees Available at Interval in QlikView

Calculating Employees Available at Interval

Fri, 19 Jun 2015 10:06:30 GMT

Hi All ,

Requirement is need to get the count of Employee based on the shift start , End TIme and Break Time.

Input:

PS ID	Date	[Log Type]	[Start Time]	[End Time]
123	6/1/2015	LogIn/LogOff	04/01/2015 05:00:00:000	04/01/2015 15:00:00:000
145	6/1/2015	LogIn/LogOff	04/01/2015 09:00:00:000	04/01/2015 18:30:00:000
178	6/1/2015	LogIn/LogOff	04/01/2015 13:00:00:000	04/01/2015 21:00:00:000
123	6/1/2015	Break	04/01/2015 08:00:00:000	04/01/2015 8:30:00:000
145	6/1/2015	Break	04/01/2015 11:00:00:000	04/01/2015 11:15:00:000
178	6/1/2015	Break	04/01/2015 17:00:00:000	04/01/2015 17:30:00:000

Break - > Break should be subtracted based on the start and End Time.

Expected Output :

Interval	Count
0 -1	0
1 -2	0
2-3	0
3-4	0
4-5	0
5-6	1
6-7	1
7-8	1
8-9	0.5
9-10	2
10-11	2
11-12	1.75
12-13	2
13-14	3
14-15	3
15-16	2
16-17	2
17-18	1.5
18-19	1.5
19-20	1
20-21	1
21-22	0
22-23	0
23-24	0

Re: Calculating Employees Available at Interval

Anonymous — Fri, 19 Jun 2015 10:17:04 GMT

Looks like you are in need of the IntervalMatch() function. This blog post by HIC explains it well.

IntervalMatch

You just need to have the interval as Hours.

Re: Calculating Employees Available at Interval

Fri, 19 Jun 2015 10:26:16 GMT

I think No. Interval Match wont work for it.

I need to find the count of employees who were available during that particular slot

Re: Calculating Employees Available at Interval

Anonymous — Fri, 19 Jun 2015 10:55:28 GMT

I use the IntervalMatch() function to do just that and it works perfectly.

Re: Calculating Employees Available at Interval

Fri, 19 Jun 2015 11:02:24 GMT

Can you share the qvw file?

Re: Calculating Employees Available at Interval

jagan — Fri, 19 Jun 2015 11:06:07 GMT

Hi,

Try this script

Temp:

LOAD

Num(Hour(StartTime)) AS StartHour,

Num(Hour(EndTime)) AS EndHour,

Minute(StartTime) AS StartMinute,

Minute(EndTime) AS EndMinute;

LOAD

[PS ID],Date([Date]) AS Date, [Log Type], TimeStamp(TimeStamp#([Start Time], 'MM/DD/YYYY hh:mm:ss:fff'), 'MM/DD/YYYY hh:mm:ss') AS StartTime,

TimeStamp(TimeStamp#([End Time], 'MM/DD/YYYY hh:mm:ss:fff'), 'MM/DD/YYYY hh:mm:ss') AS EndTime

INLINE [

PS ID, Date, Log Type, Start Time, End Time

123, 6/1/2015, LogIn/LogOff, 04/01/2015 05:00:00:000, 04/01/2015 15:00:00:000

145, 6/1/2015, LogIn/LogOff, 04/01/2015 09:00:00:000, 04/01/2015 18:30:00:000

178, 6/1/2015, LogIn/LogOff, 04/01/2015 13:00:00:000, 04/01/2015 21:00:00:000

123, 6/1/2015, Break, 04/01/2015 08:00:00:000, 04/01/2015 8:30:00:000

145, 6/1/2015, Break, 04/01/2015 11:00:00:000, 04/01/2015 11:15:00:000

178, 6/1/2015, Break, 04/01/2015 17:00:00:000, 04/01/2015 17:30:00:000];

LOAD

[PS ID], Date,

[Log Type],

StartHour + IterNo() - 1 AS Hour,

If([Log Type] = 'Break',

If(EndMinute > 0 AND StartHour + IterNo() - 1 = EndHour, -EndMinute/60, -1),

If(StartMinute > 0 AND IterNo() = 1, StartMinute/60, 1)) AS Value

RESIDENT Temp

WHILE StartHour + IterNo() - 1 <= EndHour;

Regards,

Jagan.

Re: Calculating Employees Available at Interval

Fri, 19 Jun 2015 11:13:06 GMT

HI Jagan

Can you share your file

Re: Calculating Employees Available at Interval

jagan — Tue, 23 Jun 2015 10:43:19 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.