Join on date range

[spainn]

I am aware there are posts on this in the community and that there is a macro available to do this on the public gallery, however I think this is such important functionality that it should be incorporated into the main product.  I want to be able to join 2 data sources by a date range.  In SQL the code would look like this:

select ric.*,map.*
from
Staging.TicksHourlySummary ric
LEFT OUTER JOIN Reference.ReutersInstrumentCodeMap map
on (
ric.#RIC = map.ReutersInstrumentCode
and ric.[Datetime] >= map.EffectiveFromDate
and ric.[Datetime] < map.EffectiveToDate

)

[Hugo]

Yes - the workaround (to create all dates and perform a join) can unnecessarily bloat a workflow with unneeded rows. We've had to export to SQL, do the join, then bring it back into Alteryx for some large data sets.

[cshort]

Agree - the limitation of only being able to join where one field on the left = another field on the right causes a lot of problems for us. Many of our joins require dates to be between effective start and end dates.  This is a feature even tableau prep already has.  

[ericlaug]

Perhaps the solution is to create a new kind of join tool - maybe called "SQL Join." Instead of specifying columns to pair up to test for equality, one could build a formula that specifies the ON clause. You could call the left table "a" and the right table "b" which would allow us to 

To use the example above, it could be specified as something like:

a.RIC = b.ReutersInstrumentCode
and a.[Datetime] >= b.EffectiveFromDate
and a.[Datetime] < b.EffectiveToDate

Maybe a tool like this could also be set up to allow us to specify inner or outer joins? Or am I being greedy?