Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mario-sarkis
Creator II
Creator II

Time Comparaison

Dear,

1- I have a time fields for example

Punch IN: 07:35

I need to create a field when the field is less then 07:45 in to assign the 07:45 instead of 07:35

Out Put:

Punch IN: 07:45

Punch IN : 10:00

I need to create a field when the field is grater then 09:00 in to assign the 09:00 instead of 10:00

Out Put:

Punch IN: 07:45

2- I need subtract two time fields Example:

08:30 - 07:45 = 45 minutes

08:30 - 09:00 = -30 minutes

Hope you can help

Thanks

1 Reply
Anonymous
Not applicable


Sample:

LOAD * INLINE [

    IN, OUT

07:35, 17:00

07:45, 17:00

07:55, 17:00

10:00, 17:50

10:50, 17:50

09:00, 17:50

];




ProcessSample:

Load *

,interval(Reference830-PunchIN745,'hh:mm') as Field745

,interval(Reference830-PunchIN900,'hh:mm')as Field900

;



Load *

, if(PunchDec  < Reference745, Reference745,

PunchDec) as PunchIN745

,if(PunchDec  > Reference900, Reference900,PunchDec) as PunchIN900


,if(PunchDec  < Reference745, Reference745,

if(PunchDec  > Reference900, Reference900,PunchDec)) as PunchIN

;



Load



*

,


Interval(IN,'hh:mm') AS PunchDec


,Interval(Interval#('08:30','hh:mm'),'hh:mm') AS Reference830

,Interval(Interval#('07:45','hh:mm'),'hh:mm') AS Reference745

,Interval(Interval#('09:00','hh:mm'),'hh:mm') AS Reference900


Resident Sample;



Drop Table Sample;