Join

Sharbel — Sat, 14 Jan 2023 10:02:38 GMT

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

Re: Join

MarcoWedel — Sat, 14 Jan 2023 12:47:41 GMT

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

Re: Join

Sharbel — Sat, 14 Jan 2023 16:07:21 GMT

Thanks a lot!

Re: Join

MarcoWedel — Sat, 14 Jan 2023 17:46:58 GMT

You're welcome

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

topic Join in App Development

Join

Re: Join

Re: Join

Re: Join