Forum Discussion
Luca2020
Helper I
3 years agoEmployees Stamp
Hi, I have a table with the daily entry and exit records for each employee. empl_id date type 1036 29/12/2022 14:08 IN 1036 29/12/2022 22:08 OUT 1036 31/12/2022 07:21 I...
- 3 years ago
Luca2020
Please try the following query.Actually I'm dealing with an attendance data that is much more complex than yours. I tried to simplify for your case. Hope it works.
AttendanceNew = VAR T1 = SELECTCOLUMNS ( FILTER ( AttendanceSource, AttendanceSource[tipologia] = "IN" ), "Emp.No.", AttendanceSource[empl_id], "@Date", DATEVALUE ( AttendanceSource[datd] ), "ClockIn", AttendanceSource[data] ) VAR T2 = SELECTCOLUMNS ( FILTER ( AttendanceSource, AttendanceSource[tipologia] = "OUT" ), "Emp.No.1", AttendanceSource[empl_id], "ClockOut", AttendanceSource[data] ) VAR T3 = GENERATE ( T1, VAR TimeIn = [ClockIn] VAR DateIn = DATEVALUE ( TimeIn ) VAR T4 = FILTER ( T2, [Emp.No.1] = [Emp.No.] && [ClockOut] > TimeIn ) VAR T5 = FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn ) VAR T6 = FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn + 1 ) VAR ClockOut1 = MAXX ( T5, [ClockOut] ) VAR ClockOut2 = MINX ( T6, [ClockOut] ) VAR ClockOut = COALESCE ( ClockOut1, ClockOut2 ) RETURN ROW ( "ClockOut", ClockOut ) ) RETURN T3
tamerj1
Community Champion
3 years agoLuca2020
Please try the following query.
Actually I'm dealing with an attendance data that is much more complex than yours. I tried to simplify for your case. Hope it works.
AttendanceNew =
VAR T1 =
SELECTCOLUMNS (
FILTER ( AttendanceSource, AttendanceSource[tipologia] = "IN" ),
"Emp.No.", AttendanceSource[empl_id],
"@Date", DATEVALUE ( AttendanceSource[datd] ),
"ClockIn", AttendanceSource[data]
)
VAR T2 =
SELECTCOLUMNS (
FILTER ( AttendanceSource, AttendanceSource[tipologia] = "OUT" ),
"Emp.No.1", AttendanceSource[empl_id],
"ClockOut", AttendanceSource[data]
)
VAR T3 =
GENERATE (
T1,
VAR TimeIn = [ClockIn]
VAR DateIn =
DATEVALUE ( TimeIn )
VAR T4 =
FILTER ( T2, [Emp.No.1] = [Emp.No.] && [ClockOut] > TimeIn )
VAR T5 =
FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn )
VAR T6 =
FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn + 1 )
VAR ClockOut1 =
MAXX ( T5, [ClockOut] )
VAR ClockOut2 =
MINX ( T6, [ClockOut] )
VAR ClockOut =
COALESCE ( ClockOut1, ClockOut2 )
RETURN
ROW ( "ClockOut", ClockOut )
)
RETURN
T3