Matching if a datetime is between a range of datetimes

[KanMag]

Hi, I am a little new to the world of Alteryx & after racking my brain over a problem for quite some time, I seek help. So here goes:

I am trying to find if a particular person placed an order between a set of dates. I have two input files -

• File 1, with phone numbers & order time of individuals (there can be multiple orders from one person). This is how it looks: (I have 2 million such records)

  Mobile number	Unique order ID	Order DateTime
  111111111	XX	2019-09-17 23:00:00
  111111111	XXZ	2019-09-18 00:10:00
  111111123	ZXXX	2019-09-17 20:10:00

   
• File 2, with the set of dates that I need to check the order time in. This is how it looks (1600 records)

  Event	Start time	End time
  A	2019-09-17 22:00:00	2019-09-18 00:15:00
  B	2019-09-17 22:00:00	2019-09-18 00:15:00
  C	2019-09-17 19:55:00	2019-09-17 22:10:00

  The gap between start & end time stays constant throughout the data i.e.135 mins, however, the date can be different in the columns   

I need to be able to see if order placing time of 'File 1' customers lies between any of the start & end times file 2 taken one row at a time.  So basically each order time from 'File 1" needs to be checked whether or not it falls between the start & end times stated in one row of 'File 2".

I would also require which event it corresponds to but I understand that in case of overlap of event times that might be difficult. If you guys can guide here, that would be of great help as well.  

Required output would be something like this:

Mobile number	Order DateTime	Condition match	Event
111111111	2019-09-17 23:00:00	Y	A (& B)
111111111	2019-09-18 00:10:00	Y	A (& B)
111111123	2019-09-17 20:10:00	Y	C

For me, the problem was: 

1. How to join the two files (I was going to append the file but I believe it would create a very huge dataset to manage)

2. How to check if order datetime is between the times in 'File 2' checked one row at a time

Any help is much appreciated!

[Maskell_Rascal]

Hi @KanMag 

Here is a macro that will let you do an advanced join based on a condition. In your case, I put in a formula to check that the Order DateTime was within range of the Start Time and End Time. 

[Start time]<[Order DateTime] AND [Order DateTime]<[End time]

After the join, I used a quick Summarize Tool to remove duplicate matches and Concatenate the Events that matched. 

Attached is the packaged workflow with the macro. 

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

[ArtApa · Accepted solution]

Hi @KanMag - Here is one more idea to consider:

[MarqueeCrew]

@KanMag ,

I use a GENERATE ROWS tool to create every unit of time (in your case minutes) between the event start and end times.  The formula is a little confusing and so I've created a helpful macro that allows you to point to the event table and it does the expression for you.  That macro is:  CReW Generate Date Rows 

If you take File 1 into a JOIN and beneath that, take File 2 through the macro and connect that macro to the same JOIN, you can join on Order Date Time = Out Date.

You'll configure the macro to increment by minutes and it will be creating every minute for each event.  You will join only on the exact minute that matches (if any) and you'll get a EVENT field that matches.

Cheers,

Mark

[KanMag]

@ArtApa Simplest solution, you're a savior!

Thank you everyone, you've been a great help!

[basubb]

This is an elegant solution, and I was getting an error when using "days" as a parameter for day increments in DateTimeAdd([Start_date],1,"day") but "day" worked.

Thanks

Basu