How to find Count of employees by 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

[mightyqlikers]

hi

By what time emplyee should reach the office .

based on that create time difference between timein-expectedtime.

Regards
$@M

[#3]

Here the employee has to reach at 9:00 AM after 9 he is considered as Late comer after 9 if  i select <5minutes it has to show how many members are late by 5 minutes 

[SreeniJD]

Hi Hemeswar,

Are you looking for a QVW?

Try this - Use intervalmatch function to compare and get the desired results..

Sreeni

[#5]

please send the code i don't have idea

[mightyqlikers]

Hi

PFA.

Hope it helps

[#7]

please send the code i didn't have licensed version

[SreeniJD]

LOAD * INLINE [

    EMPID, EMPINDATE, TIMEIN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

];

LOAD

  *,

  if(LateBy <= 5,'<5 Min',if(LateBy > 5 and LateBy <=10 ,'5-10 Min',if(LateBy > 10 and LateBy <=15 ,'10-15 Min',if(LateBy > 15 and LateBy <=20 ,'15-20 Min',

  if(LateBy > 20 and LateBy <=30 ,'20-30 Min',if(LateBy > 30 ,'>30 Min')))))) as Duration;

LOAD

  *,

  (Hour(Timestamp#(TIMEIN,'HH:MM TT'))*60)+ (Minute(Timestamp#(TIMEIN,'HH:MM TT')))-540 as LateBy;

/*Code from Sam*/

[Digvijay_Singh]

Is this same like Re: How to find no of employees entering late by interval

I think this can also be closed now as above.

[mightyqlikers]

Hi Please find the below code

LOAD

  *,

  if(LateBy <= 5,'<5 Min',

  if(LateBy > 5 and LateBy <=10 ,'5-10 Min',

  if(LateBy > 10 and LateBy <=15 ,'10-15 Min',

  if(LateBy > 15 and LateBy <=20 ,'15-20 Min',

  if(LateBy > 20 and LateBy <=30 ,'20-30 Min',

  if(LateBy > 30 ,'>30 Min')))))) as Duration;

LOAD

  *,

  (Hour(Timestamp#(TIMEIN,'HH:mm TT'))*60)+ (Minute(Timestamp#(TIMEIN,'HH:mm TT')))-540 as LateBy;

LOAD * INLINE [

    EMPID, EMPINDATE, TIMEIN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

];

take all the fields in table box.

Regards

$@M