Alteryx Designer Discussions

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

Splitting Date Time and Consolidating Records

StephR23
7 - Meteor

Hi all! My dataset currently has 1 field for "Date Time" and another with activity. I'm trying to split the date time field into (1) date and (2) time and then consolidate the records into one record so I can calculate the duration per event (event being open to close timeframe).

 

Current data structure:

Record #Date TimeLocation IDActivity
12022-01-03 17:33:071001Open
22022-01-03 18:52:551001Close
32022-01-05 06:38:231002Open
42002-01-05 07:15:051002Close

 

Trying to get to this:

Prior RecordsDateLocationOpen TimeClose TimeDuration
1 & 21/3/2022100117:33:0718:52:5501:19:48
3 & 41/5/2022100206:38:2307:15:0500:36:42

 

Does anyone know how to do this? Thanks in advance!!

10 REPLIES 10
binuacs
12 - Quasar

@StephR23 

binuacs_0-1642027552663.png

 

gabrielvilella
11 - Bolide

Hi @StephR23, you don't acctually need to split your data since you need to calculate the difference between two dates after. You need that on a single field. Also, this workflow will still works if the duration is higher or equals to 24h.

Qiu
18 - Pollux

@StephR23 
I have another try.

Calculating Time difference in Iso format is not really alteryx strong point. 🤣

0113-SRuhl.PNG

atcodedog05
21 - Polaris

Hi @StephR23 

 

Here is my take.

 

Workflow:

atcodedog05_0-1642055679113.png

 

Hope this helps : )

 

StephR23
7 - Meteor

Thank you all so much for your quick and helpful responses!! A couple of these seem to be doing the same thing just in different ways. Since I'm only able to select one response as a solution, I'm just selecting the first that was provided and works. Grateful to have such a helpful and kind community!! 

atcodedog05
21 - Polaris

Hi @StephR23 

 

The best thing is you can actually mark more than one post as solution 😀 so feel free to.

 

Happy to help : )

Cheers and have a nice day!

Qiu
18 - Pollux

@StephR23 
So please also the ones from me and @atcodedog05  😁

StephR23
7 - Meteor

So... as I was working with these solutions, all of them only deal with instances where there are two records in a given day. What can I change so that when there are multiple open/close events in one 24-hour period, that they show as 2 separate records?

 

Current data structure:

Record # Date Time Location ID Activity
1 2022-01-03 17:33:07 1001 Open
2 2022-01-03 18:52:55 1001 Close
3 2022-01-03 19:38:23 1001 Open
4 2022-01-03 20:15:05 1001 Close


Trying to get to this:

Prior Records Date Location Open Time Close Time Duration
1 & 2 1/3/2022 1001 17:33:07 18:52:55 01:19:48
3 & 4 1/3/2022 1001 19:38:23 20:15:05 00:36:42

Right now, from the provided responses, records are being grouped by day, so the duration is not calculating correctly. Thanks!

gabrielvilella
11 - Bolide

Hi @StephR23, here is a solution for that.

Labels