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 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-05 06:38:23 | 1002 | Open |
4 | 2002-01-05 07:15:05 | 1002 | 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/5/2022 | 1002 | 06:38:23 | 07:15:05 | 00:36:42 |
Does anyone know how to do this? Thanks in advance!!
Solved! Go to Solution.
Hi @Steph23, 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.
@Steph23
I have another try.
Calculating Time difference in Iso format is not really alteryx strong point. 🤣
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!!
Hi @Steph23
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!
@Steph23
So please also the ones from me and @atcodedog05 😁
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!
Hi @Steph23, here is a solution for that.