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

SOLVED: Find overlapping times by specific user

Jake_NG
7 - Meteor

Hi,

 

I have a dataset which contains time periods by individual users - sample below. What I am looking to do is identify the amount of time that periods overlap for a specific user. For example, I would like to know that user 001 has an overlap/duplicate time of 3:30hrs on 1/1/2021.

 

User IDDateStart timeEnd Time
0011/1/202108:0012:00
0011/1/202108:3012:00
0021/1/202108:0012:00

 

I have been looking at this post about overlapping timestamps which is very similar to my situation, except for the user ID part, the solution there helps you find any overlap between different users but I want the exact opposite of that and when I summarize I lose the user ID granularity.

 

My first thought was to add user ID to the "formula" that combines start time and start date (eg. 001 1/1/2021 08:00) and generate rows like that but that obviously won't work since down the line it does time comparisons and you can't combine strings with date/time.

 

I looked at the solution from this post about finding concurrent date ranges so I put the data into the following format with one row per user and all the times in columns:

User IDDateStart time 1Start time 2End time 1End time 2
0011/1/202108:0008:3012:0012:00
0021/1/202108:30 12:00 

But then when I have to calculate the part below as described in the post, I have up to 10 start/end time entries for some users and am unsure how to compare each time when there are different amounts of entries by each user in a day (some have 1-2, others have many more)

 

IF (DateTimeDiff([Begin2],[Begin1],"seconds") > 0 &&
DateTimeDiff([Begin2],[End1],"seconds") < 0) ||
(DateTimeDiff([End2],[Begin1],"seconds") > 0 &&
DateTimeDiff([End2],[End1],"seconds") < 0) THEN 1 ELSE 0 ENDIF

 What would the wise folks of this forum advise?

 

Much appreciated!

5 REPLIES 5
kelsey_kincaid
12 - Quasar

Hi @Jake_NG ,

My solution is very similar to @BenMoss in the post you linked here: overlapping timestamps 

 

The main difference is that I added 'UserId' as a groupby in the summarize tool and added a couple of formulas specific to your use case.

 

Let me know if this does/doesn't work for you and if you have any questions!

 

kelsey_kincaid_0-1611098566224.png

 

Qiu
20 - Arcturus
20 - Arcturus

@Jake_NG 
Maybe I am missing something?

0120-Jake_NG.PNG

DavidP
17 - Castor
17 - Castor

Hi @Jake_NG 

 

You only need a small adjustment to the solution provided by Ben Moss:

 

DavidP_0-1611099359240.png

 

Jake_NG
7 - Meteor

Wow, amazingly prompt response! I will give it a shot and report back. This community is great.

Jake_NG
7 - Meteor

Kelsey that worked - thank you so much! The only tweak I had to make was to change the final formula to be

IF [Count]>1 THEN [count] ELSE 0 ENDIF

 

I was interested in counting any overlap at all so that made if there were more than 2 overlaps, it would count the third since in the original it would discount any duplicate if it was present more than once. Much appreciated!

Labels