5 Replies Latest reply: Oct 7, 2015 8:53 AM by Digvijay Singh

# How to find no of employees entering late by interval

Hi,Qlikers

i have emp Table:

Empid,Date,Timein

101,10/1/2015,9:00 AM

102,10/1/2015,9:10 AM

103,10/1/2015,9:05 AM

104,10/1/2015,9:20 AM

105,10/1/2015,9:40 AM

101,10/2/2015,9:15 AM

102,10/2/2015,8:45 AM

103,10/2/2015,9:25 AM

104,10/2/2015,9:35 AM

105,10/2/2015,9:40 AM

101,10/3/2015,9:01 AM

102,10/3/2015,8:55 AM

103,10/3/2015,9:25 AM

104,10/3/2015,9:22 AM

105,10/3/2015,9:45 AM

and

Table2:

LateBy

<5min,

5-10min

10-15min

15-20min

20-30min

>30min

if the user select a date and late by fields(i.e 10/1/2015 ,10-15min) then it has to show how many employees are late by 10-15 min on 10/1/2015

Here the employee usual timing is 9:00 AM

• ###### Re: How to find no of employees entering late by interval

Hemeswar -

I suggest you please post only one question for one issue to avoid duplicates

Sreeni

• ###### Re: How to find no of employees entering late by interval

Check this out -

Let vInTime = Frac(Time#('9:00 AM','h:mm tt'));

Table1:

Load Empid,Date,Time(Timein,'h:mm tt') as Timein,interval(Timein-('\$(vInTime)'),'h:mm') as LateDuration;

Date#(Date,'MM/DD/YYYY') as Date,

Frac(Time#(Timein, 'h:mm tt')) as Timein

inline [

Empid,Date,Timein

101,10/1/2015,9:00 AM

102,10/1/2015,9:10 AM

103,10/1/2015,9:05 AM

104,10/1/2015,9:20 AM

105,10/1/2015,9:40 AM

101,10/2/2015,9:15 AM

102,10/2/2015,8:45 AM

103,10/2/2015,9:25 AM

104,10/2/2015,9:35 AM

105,10/2/2015,9:40 AM

101,10/3/2015,9:01 AM

102,10/3/2015,8:55 AM

103,10/3/2015,9:25 AM

104,10/3/2015,9:22 AM

105,10/3/2015,9:45 AM ];

Table2:

Load LateBy,if(LateBy='<5','00:00',if(LateBy='>30','00:30','00:' & SubField(LateBy,'-',1))) as Begin,

if(LateBy='<5','00:05',if(LateBy='>30','05:00','00:' & SubField(LateBy,'-',2))) as End;

Load PurgeChar(LateBy,'min') as LateBy inline [

LateBy

<5min

05-10min

10-15min

15-20min

20-30min

>30min ];

IntervalMatch:

IntervalMatch(LateDuration)