Good Morning All, Taking gate data, I am trying to work out how long individuals spend on site (Preferably in a single go so happy to record multiple times on site for the same day/shift. Useful things to know: * Over 10,000 gate movements a day (so person does not enter / leave site on the next row) * May work a night shift or day shift and may flip between them * Multiple different gates * Person may exit site and not return for 20 days * Person may enter and exit site multiple times in a day My data format is shown as the following: Record IDNameGateWork DateWork SiteIN/OUTURN50Matt SmithAlpha10/07/202221:35:01IN20548636 Some time much later........ ...... ..... ......... .....2500Matt SmithBeta11/07/202205:35:26OUT20548636 How would you do this given information and useful things to know? I was hoping to achieve something similar to: NameGate InGate OutDate/Time Stamp InDate/Time Stamp OutHH:MMURNMatt SmithAlphaBeta10/07/2022 21:35:0111/07/2022 05:53:2608:0020548636 I was thinking that i could create time/date stamp, organize by this and then match via URN but its how to match to next timestamp for that URN that causing my brainache this morning Thank you

Matching Gate Time Stamp Records

Good Morning All,

Taking gate data, I am trying to work out how long individuals spend on site (Preferably in a single go so happy to record multiple times on site for the same day/shift. Useful things to know:

Over 10,000 gate movements a day (so person does not enter / leave site on the next row)
May work a night shift or day shift and may flip between them
Multiple different gates
Person may exit site and not return for 20 days
Person may enter and exit site multiple times in a day

My data format is shown as the following:

Record ID	Name	Gate	Work Date	Work Site	IN/OUT	URN
50	Matt Smith	Alpha	10/07/2022	21:35:01	IN	20548636
Some time much later........	......		.....	.....	....	.....
2500	Matt Smith	Beta	11/07/2022	05:35:26	OUT	20548636

How would you do this given information and useful things to know? I was hoping to achieve something similar to:

Name	Gate In	Gate Out	Date/Time Stamp In	Date/Time Stamp Out	HH:MM	URN
Matt Smith	Alpha	Beta	10/07/2022 21:35:01	11/07/2022 05:53:26	08:00	20548636

I was thinking that i could create time/date stamp, organize by this and then match via URN but its how to match to next timestamp for that URN that causing my brainache this morning

Thank you

Developer

Developer Tools

Accepted answers

All comments

grazitti_sapna

Hello @Bobbins, would it be possible for you to provide a sample dataset?

Thanks!

grazitti_sapna

Hi @Bobbins, since there is not much data so I tried to create something from your inputs.

Give this a try and let us know if it works for you?

Thanks!

Bobbins_workflow.yxmd

OllieClarke

HI @Bobbins how's this for a solution:

I've grouped each session per person based on IN/OUT, If you want total time worked per person, then you can get that from the offshoot at the end:

Hope that helps,

Ollie

URN.yxmd

Bobbins

Hi All,

Apologies, should have included more data. @grazitti_sapna so now attached

@OllieClarke That almost works but when i try it with the dummy data it falls apart on some rows?

Dummy timestamp.yxzp

grazitti_sapna

@Bobbins, is this what you are looking for?

I hope this helps!

Thanks!

Dummy timestamp.yxmd

OllieClarke

Hey @Bobbins

the reason a couple of rows don't work properly is that there are instances where someone has checked in, but not out (or vice versa)

Will that be possible in your actual data set?

URN.yxmd

Bobbins

@grazitti_sapna Almost perfect, although there is the problem that @OllieClarke correctly picks up on. So yes, this is real time/dates just name and numbers changed. It occurs because most likely this individual has incorrectly exited through a turnstile (IE some of them are large swing gates and although you should correctly do it one at a time, obviously some people dont!)

In addition, because we will look at this data in a non continous snapshot kind thing, some people will be seen as leaving or exiting without the other occurring. Not sure how to count this out really and hence I am not sure how to solve this, the below highlights the problem using @grazitti_sapna answer.

I dont think there is a suitable answer, if not, will mark the above as the correct answer. Thank you

grazitti_sapna

@Bobbins, thanks for notifying I have made the changes and please take a look if this workflow solves your purpose.

Thanks!

Dummy timestamp Updated.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703