How to find early comers and over time sprnding workers for the timings given

[#1]

Data:
LOAD
ID,EventID,EmpID,Timestamp#(Timings,'MM/DD/YYYY hh:mm TT') as Timings,
Date(floor(Timestamp#(Timings,'MM/DD/YYYY hh:mm TT'))) as Date
INLINE [
ID, EventID, EmpID, Timings
1, 0, 101, 8/9/2015 7:00 AM
2, 1, 101, 8/9/2015 11:30 AM
3, 0, 101, 8/9/2015 2:00 PM
4, 1, 101, 8/9/2015 5:45 PM
5, 0, 102, 8/9/2015 8:45 AM
6, 1, 102, 8/9/2015 11:45 AM
7, 0, 102, 8/9/2015 2:00 PM
8, 1, 102, 8/9/2015 5:30 PM
9, 0, 101, 8/16/2015 7:20 AM
10, 1, 101, 8/16/2015 2:20 PM
11, 0, 102, 8/16/2015 8:10 AM
12, 1, 102, 8/16/2015 11:45 AM
];

for this how to find early comers(those who comes before 7:10 AM) and over time spending workers(those who are working after 4:00 PM) i am trying to store those timings(7:10 AM and 4:00 PM ) in variables(vEarlyTime,vOverTime) and using if condition  like if(Timings<$(vEarlyTime,time )as earlycomers and if(Timings>vOverTime,Time)as OverTime but it is showing error

can any one help how to find

Thanks in advanse

[sunny_talwar]

EmpID 101 comes before 7:10 on 8/9, but comes at 7:20 on 8/16. Is he early comer? Need to give your expected output to fully understand what you are trying to do.

Best,

Sunny

[maxgro]

I think you can add the bold to your script

set vEarlyTime=(7*60+10)/(24*60);

set vOverTime=16/24;

Data:

LOAD

  *,

  if(frac(Timings)<=$(vEarlyTime),1,0) as FlagEarly,

  if(frac(Timings)>=$(vOverTime),1,0) as FlagOver;

LOAD

ID,EventID,EmpID,Timestamp#(Timings,'MM/DD/YYYY hh:mm TT') as Timings,

Date(floor(Timestamp#(Timings,'MM/DD/YYYY hh:mm TT'))) as Date

INLINE [

ID, EventID, EmpID, Timings

1, 0, 101, 8/9/2015 7:00 AM

2, 1, 101, 8/9/2015 11:30 AM

3, 0, 101, 8/9/2015 2:00 PM

4, 1, 101, 8/9/2015 5:45 PM

5, 0, 102, 8/9/2015 8:45 AM

6, 1, 102, 8/9/2015 11:45 AM

7, 0, 102, 8/9/2015 2:00 PM

8, 1, 102, 8/9/2015 5:30 PM

9, 0, 101, 8/16/2015 7:20 AM

10, 1, 101, 8/16/2015 2:20 PM

11, 0, 102, 8/16/2015 8:10 AM

12, 1, 102, 8/16/2015 11:45 AM

];

[#4]

if we click on the day(8/9 or 8/16) in that day whether the employee is late comer or not we have to check

[#5]

not getting the desired results

[sunny_talwar · Accepted solution]

See if this helps:

Script:

Data:

LOAD ID,

  EventID,

  EmpID,

  Timestamp#(Timings,'MM/DD/YYYY hh:mm TT') as Timings,

  Time(Frac(Timestamp#(Timings,'MM/DD/YYYY hh:mm TT'))) as Time,

  Date(floor(Timestamp#(Timings,'MM/DD/YYYY hh:mm TT'))) as Date

INLINE [

ID, EventID, EmpID, Timings

1, 0, 101, 8/9/2015 7:00 AM

2, 1, 101, 8/9/2015 11:30 AM

3, 0, 101, 8/9/2015 2:00 PM

4, 1, 101, 8/9/2015 5:45 PM

5, 0, 102, 8/9/2015 8:45 AM

6, 1, 102, 8/9/2015 11:45 AM

7, 0, 102, 8/9/2015 2:00 PM

8, 1, 102, 8/9/2015 5:30 PM

9, 0, 101, 8/16/2015 7:20 AM

10, 1, 101, 8/16/2015 2:20 PM

11, 0, 102, 8/16/2015 8:10 AM

12, 1, 102, 8/16/2015 11:45 AM

];

Straight Table

Dimension: EmpID

Expressions:

1) Time In: =Min({<Date = {"$(=Date(Max(Date)))"}, EventID = {0}>}Time)

2) Time Out: =Max({<Date = {"$(=Date(Max(Date)))"}, EventID = {1}>}Time)

3) Early Comer: =If(Min({<Date = {"$(=Date(Max(Date)))"}, EventID = {0}>}Time) < Time#('7:10 AM', 'h:mm TT'), 'Early Comer', 'Late Comer')

4) Over Time =If(Max({<Date = {"$(=Date(Max(Date)))"}, EventID = {1}>}Time) > Time#('4 PM', 'h TT'), 'Over Time', 'Under Time')

Output with no selection:

Output with 8/9 Selected:

Output with 8/16 Selected:

I hope this is what you are looking for.

Best,

Sunny

[maxgro]

why not?

[#8]

Thank you very much sunny for replying me

[#9]

Now i am getting thanks for reply

[sunny_talwar]

Not a problem

I am glad you got what you were looking for.

Best,

Sunny