Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following Table:
Note: 100 stands for IN and 200 stands for OUT
I am trying to transform the above table into the following table:
any ideas would be appreciable.
many thanks,
sharbel
One solution using a join might be:
table1:
LOAD *,
If(emp_No=Previous(emp_No),If(Id=100,Peek(PresenceID)+1,Peek(PresenceID)),1) as PresenceID
Inline [
emp_No, date, time, Id
1228, 13/01/2023, 06:11:47, 100
1228, 13/01/2023, 12:48:48, 200
1228, 13/01/2023, 15:17:54, 100
1228, 13/01/2023, 17:11:07, 200
1228, 16/01/2023, 06:20:00, 100
1228, 16/01/2023, 17:10:00, 200
1228, 17/01/2023, 06:30:00, 100
1228, 17/01/2023, 08:15:00, 200
1228, 17/01/2023, 09:45:00, 100
1228, 17/01/2023, 13:20:00, 200
1228, 17/01/2023, 13:50:00, 100
1228, 17/01/2023, 16:30:00, 200
1229, 13/01/2023, 07:11:47, 100
1229, 13/01/2023, 13:48:48, 200
1229, 13/01/2023, 16:17:54, 100
1229, 13/01/2023, 18:11:07, 200
1229, 16/01/2023, 07:20:00, 100
1229, 16/01/2023, 18:10:00, 200
1229, 17/01/2023, 07:30:00, 100
1229, 17/01/2023, 09:15:00, 200
1229, 17/01/2023, 10:45:00, 100
1229, 17/01/2023, 14:20:00, 200
1229, 17/01/2023, 14:50:00, 100
1229, 17/01/2023, 17:30:00, 200
];
table2:
LOAD emp_No,
PresenceID,
date,
time as Time_IN
Resident table1
Where Id=100;
Join
LOAD emp_No,
PresenceID,
date,
time as Time_OUT
Resident table1
Where Id=200;
DROP Table table1;
hope this helps
Marco
One solution using a join might be:
table1:
LOAD *,
If(emp_No=Previous(emp_No),If(Id=100,Peek(PresenceID)+1,Peek(PresenceID)),1) as PresenceID
Inline [
emp_No, date, time, Id
1228, 13/01/2023, 06:11:47, 100
1228, 13/01/2023, 12:48:48, 200
1228, 13/01/2023, 15:17:54, 100
1228, 13/01/2023, 17:11:07, 200
1228, 16/01/2023, 06:20:00, 100
1228, 16/01/2023, 17:10:00, 200
1228, 17/01/2023, 06:30:00, 100
1228, 17/01/2023, 08:15:00, 200
1228, 17/01/2023, 09:45:00, 100
1228, 17/01/2023, 13:20:00, 200
1228, 17/01/2023, 13:50:00, 100
1228, 17/01/2023, 16:30:00, 200
1229, 13/01/2023, 07:11:47, 100
1229, 13/01/2023, 13:48:48, 200
1229, 13/01/2023, 16:17:54, 100
1229, 13/01/2023, 18:11:07, 200
1229, 16/01/2023, 07:20:00, 100
1229, 16/01/2023, 18:10:00, 200
1229, 17/01/2023, 07:30:00, 100
1229, 17/01/2023, 09:15:00, 200
1229, 17/01/2023, 10:45:00, 100
1229, 17/01/2023, 14:20:00, 200
1229, 17/01/2023, 14:50:00, 100
1229, 17/01/2023, 17:30:00, 200
];
table2:
LOAD emp_No,
PresenceID,
date,
time as Time_IN
Resident table1
Where Id=100;
Join
LOAD emp_No,
PresenceID,
date,
time as Time_OUT
Resident table1
Where Id=200;
DROP Table table1;
hope this helps
Marco
Thanks a lot!
You're welcome
Please close your thread by accepting a solution if your issue is solved, thanks