Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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

 

5 REPLIES 5
OllieClarke
15 - Aurora
15 - Aurora

@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
17 - Castor
17 - Castor

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
20 - Arcturus
20 - Arcturus

@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
15 - Aurora
15 - Aurora

@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