Join

[Sharbel]

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

[MarcoWedel · Accepted solution]

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

[Sharbel]

Thanks a lot!

[MarcoWedel]

You're welcome

Please close your thread by accepting a solution if your issue is solved, thanks