Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating Employees Available at Interval

Hi All ,

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

Input:

  

PS IDDate[Log Type][Start Time][End Time]
1236/1/2015LogIn/LogOff04/01/2015 05:00:00:00004/01/2015 15:00:00:000
1456/1/2015LogIn/LogOff04/01/2015 09:00:00:00004/01/2015 18:30:00:000
1786/1/2015LogIn/LogOff04/01/2015 13:00:00:00004/01/2015 21:00:00:000
1236/1/2015Break04/01/2015 08:00:00:00004/01/2015 8:30:00:000
1456/1/2015Break04/01/2015 11:00:00:00004/01/2015 11:15:00:000
1786/1/2015Break04/01/2015 17:00:00:00004/01/2015 17:30:00:000

Login/Logoff - > Shift start and end of each employee

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

Expected Output :

 

IntervalCount
0 -10
1 -20
2-30
3-40
4-50
5-61
6-71
7-81
8-90.5
9-102
10-112
11-121.75
12-132
13-143
14-153
15-162
16-172
17-181.5
18-191.5
19-201
20-211
21-220
22-230
23-240
7 Replies
Anonymous
Not applicable
Author

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.

Not applicable
Author

I think No. Interval Match wont work for it.

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

Anonymous
Not applicable
Author

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

Not applicable
Author

Can you share the qvw file?

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

HI Jagan

Can you share your file

jagan
Luminary Alumni
Luminary Alumni

Hi,

Please find attached file for solution.

Regards,

Jagan.