ETL Magic - Join on Date Range?

user007486

Hello Dojo,

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?

 

Thanks,

 

Jason

GrantSmith

Hi @user007486 

 

You're correct. The Join tile in Magic ETL only supports direct equality and doesn't handle BETWEEN type joins. What you could do is do your ID join and then utilize a filter tile to restrict the records after the join but might be a bit inefficient since it's reading all of those records and then doing a filter on them instead of doing the join and filter at the same time.

GrantSmith

Hi @user007486 

 

You're correct. The Join tile in Magic ETL only supports direct equality and doesn't handle BETWEEN type joins. What you could do is do your ID join and then utilize a filter tile to restrict the records after the join but might be a bit inefficient since it's reading all of those records and then doing a filter on them instead of doing the join and filter at the same time.

user007486

Yeah that's a decent idea for a work around.  Unfortunately the volume of data I'm working with I'm not sure I wanna cross join like that.  I'll come up with a different work around.  Thanks for the input.

jaeW_at_Onyx

how wide are your start and end dates?

for the sake of example say you have a row where one row represents project (with a start and end date).

 

If you calculate datediff between start and end date, (let's say 12 days) then you could join to a dataset that is already blown out.

 

So Blow out table would be

Join_Col, NUM

1 ,1

2, 1

2,2

3,1

3,2

3,3

 

You join DateDiff to Join_Col.  It will duplicate your Project rows 12 times then just DATE_ADD(Start Date, num DAYS)

 

Success.