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) EventStart timeEnd timeA2019-09-17 22:00:002019-09-18 00:15:00B2019-09-17 22:00:002019-09-18 00:15:00C2019-09-17 19:55:002019-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 numberOrder DateTimeCondition matchEvent111111111 2019-09-17 23:00:00 YA (& B)111111111 2019-09-18 00:10:00 YA (& B)111111123 2019-09-17 20:10:00 YC For me, the problem was: * 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) * How to check if order datetime is between the times in 'File 2' checked one row at a time Any help is much appreciated!

Matching if a datetime is between a range of datetimes

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 (&
111111111	2019-09-18 00:10:00	Y	A (&
111111123	2019-09-17 20:10:00	Y	C

For me, the problem was:

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)
How to check if order datetime is between the times in 'File 2' checked one row at a time

Any help is much appreciated!

Datasets

Date Time

Join

Accepted answers

ArtApa

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

MatchDateTimeAA.yxmd

All comments

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

Match DateTime Between Ranges.yxzp

ArtApa

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

MatchDateTimeAA.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703