ETL Magic - Join on Date Range?
Looking for a solution to the following problem.
I have a reference data set that has multiple spans of time, StartDate and EndDate, for a given ID.
I was hoping to join to this via ETL Magic, but it looks like currently it only supports explicit joins between columns, correct?
For example, I'm trying to reproduce a join such as:
FROM Main M
LEFT OUTER JOIN Reference R
ON M.ID = R.ID
AND M.Date BETWEEN R.StartDate AND R.EndDate
I would try and pivot the data prior to joining, but i have a variable number of spans in the reference table.
Anyone have any other suggestions on how to solve this inside of Domo?