ETL Magic - Join on Date Range?

Reply
Highlighted
Yellow Belt

ETL Magic - Join on Date Range?

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


Accepted Solutions
Highlighted
Red Belt

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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Highlighted
Red Belt

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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Highlighted
Yellow Belt

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.

Highlighted
Black Belt

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.


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.