Conditional Join - Multiple Criteria Plus Within Date Range

[cemcintosh2]

I need to have two tables joined.

Table A is just a simple table where a company is matched to a parent company.  The parent company can change at any time, so there are effective start and end dates.  For example there is often the same company listed with a different parent at different time periods.  The company can only have a single parent at a given time.

Table B is a transaction table that has transactions at the company level.  

I need to match the company name from Table A to the Transaction Table data, but only if the transaction date falls within the range that the the Parent is attached to the company.  See the example tables and desired output.  

Thanks for any help!

[gawa]

hi @cemcintosh2 

JOIN+FILTER would help.

You can join tables by 'Company Name', and filter inner join data by date as per criteria you mention.

Alteryx does not support conditional join in a single tool, but such a function is something  nice to have for SQL familiar users.

Idea to realize custom join is posted here for your reference.(though not yet accepting votes) 

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Join-by-Custom-Conditions/idi-p/1174...