Matching transactions that occur within a 30 day window

[zemariajudice]

Hello everyone,

I am currently trying to match transactions for a same product (the dataset has thousands of different products) that occurred within a 30 day window. Picture the following example:

Transaction 1

Product A

30 April 2015

Transaction 2

Product A

1 May 2015

Transaction 3

Product A

1 August 2015

Here, only Transactions 1 and 2 would be relevant. In other words, I assume I need to filter transactions that had no other for the same product in the 30 days prior/subsequent to it. Is this the best way to approach the problem?

Thank you for your help!

Best

[JordanB]

Hi @zemariajudice

I have attached a workflow with annotations describing what I believe to be the process below. Hopefully this can guide you in the right direction to make modifications if needed. 

You will first need to make sure your current date fields are in an Alteryx supported format. The Date Time tool can help you here, however if you want a little more flexibility try out this great Parse Dates Macro from @AlexKo which deals with your date format very well without much additional manipulation. 

Hope this helps,

Best,

Jordan Barker

Solutions Consultant 

[JessicaS]

You could convert your date fields to altery date fields using the regex tool and the datetime tool.  From there you should be able to do a join followed by a datetime difference function to give you the difference in days between the two ocurrences. 

[zemariajudice]

@JordanB

thank you very much, but I am not being able to open the workflow as I am using v10.0. Any workaround for this? Thanks

[JordanB · Accepted solution]

@zemariajudice I have attached a version you should be able to open in 10.0. 

@JessicaS workflow is another good solution so please take a look at both and they should offer some good ideas. 

Best,

Jordan Barker

Solutions Consultant