ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Calculating duration of time between Entry and Exit across Rows with data anomalies

gavin_chin
6 - Meteoroid

i have car parking entry and exit data from our car park operator based on 'swipe cards'. I would like to calculate the duration of time between entry and exit for each particular set of instances (one entry and one exit per instance). However, the data layout and data itself is quite quirky and i am unsure how to resolve.

- Parking can sometimes occur across 2 days (entry 5pm Monday Exit 5am Tuesday)
- The entry and exit data is all presented in the same column
- sometimes there are anomalies with the data e.g. an Entry appears but no Exit swipe for 3/2/2021 - is there a way to assume an exit after 24 hours and create a dummy "exit entry'?

i set out an example below. 

gavin_chin_0-1614078059155.png

 

OllieClarke
13 - Pulsar
13 - Pulsar

@gavin_chin Here's my approach:

OllieClarke_0-1614079049737.png

It assumes that there will be an Entry per visit, even if there isn't an Exit. Hopefully that's a fair assumption and this helps 🙂

Emil_Kos
15 - Aurora

Hi @OllieClarke,

 

A really elegant solution. I was working on my won workflow but your is so nice that I don't think I need to create my won. 

Qiu
17 - Castor

@gavin_chin 
If your date is in good order.

Capture44.PNG

gavin_chin
6 - Meteoroid

@OllieClarke Thanks for the quick response Ollie!  much appreciated.

 

what happens if sometimes there is also an "exit" but not an "entry"

 

im not sure if its a glitch, or if its for some other unplanned reason e.g. someone physically walking out of the carpark to have a break or something

OllieClarke
13 - Pulsar
13 - Pulsar

@gavin_chin if there can be exits, but no entries, then it gets trickier. Basically we need *something* to signify a new session. I guess you could construct some logic that creates a new ID every time there's an entry, or if you have consecutive rows of the same (e.g. Exit,Exit or Entry,Entry). Alternatively you could try and do something with the datetimes and assumptions of how long sessions are, but I think that would be less accurate

Labels