community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Join on date range

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

)

 

 

3 Comments
Alteryx Certified Partner

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.

Atom

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.  

Meteor

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?