How to find no of employees entering late by interval

[#1]

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

please send me the reply

Thanks in advanse

Here the employee usual timing is 9:00 AM

[SreeniJD]

Hemeswar -

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

Sreeni

[#3]

ok please send me reply it's urgent

[Digvijay_Singh · Accepted solution]

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;

Load Empid,

  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)

Load distinct Begin,End resident Table2;

[#5]

Thank you brother now i am getting the desired results

[Digvijay_Singh]

I am glad it worked for you.