Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Join two tables with date range option

Highlighted
8 - Asteroid
Hi all,

I'm trying to implement a simple JOIN of two tables on a date range.

Here is the scenario:

 i) Transactions table got transactions for every day.
Date + Product + Quantity
2014-01-01     PRODUCT1     100
2014-06-01     PRODUCT1     150
ii) Reference table got Product's multiplication factor for different date ranges:

Product + StartDate + EndDate + MultiplicationFactor
PRODUCT1    2012-01-01    2014-01-31  0.5
PRODUCT1    2014-02-01    2015-01-31  0.6

When i join both the tables I need to join on Product & the TransactionDate falls between StartDate & EndDate of Reference table.
O/P:

2014-01-01 PRODUCT1 100*0.5
2014-06-01 PRODUCT1 150*0.6

I know in T-SQL or in PL/SQL it is straightforward.
Pesudo code:

Transactions INNER JOIN Reference
ON Transactions.Product = Reference.Product
AND Transactions.Date BETWEEN Reference.StartDate AND Reference.EndDate

I was wondering how to implement this functionality within Alteryx.

Thanks,
Sandeep.



Highlighted
7 - Meteor
Might not be the most efficient way (may take some time to run), but you could just join on product and use a filter after the join to only keep the rows where the date falls within the range.

If anyone knows a better way,.. I'd love to know as well.  Good question.
Highlighted
7 - Meteor
I just thought of another way.  You could try the insert rows tool on your reference table and include a row for every single date in the range.  Product and multiplier would repeat.  Then join on product and date.

Might be worth a shot.
Highlighted
Alteryx Community Team
Alteryx Community Team
I made a macro that can do what you're after in one step. You can find it here: http://www.alteryx.com/community/blogs/engine-works/advanced-join-macro
Let me know what you think...
-Neil
Highlighted
Thanks Neil for the macro, works well. 
Any chance you could add couple more joi fields?
I have more than 1 field to join (up to 3) in addition to the filter?

Thanks
Sheron
 
Highlighted
5 - Atom

Hi Neil--

 

I get an error that tells me 30 records were generated by an N-dimensional Join and stops calculating rows. Is there a limit to the rows the Macros will calculate? 

Highlighted
16 - Nebula
16 - Nebula

Not sure it is the most efficient but the way I worked round this last time I needed to do this was to use the Generate Rows tool to expand the reference table to have a row for each day:

 

Tool configuration something like:

config.png

 

Its then trivial to join to the transactions table.

 

Worked quite well in the limited case I was dealing with. Stuck it together with the sample data you posted.

 

James

Highlighted
12 - Quasar
12 - Quasar

@FDELABAL If this result is really desired, then you can open the Macro and go to the Join Multiple Tool(s) and change the "Cartesian Joins" option to "Allow All multidemensional Joins". However, be careful with this as your temp drive may fill up quickly. Make sure either your Temp folder either doesn't reside on the OS drive or watch the process closely to make sure that you don't crash your system.

Labels