ETL Magic - Join on Date Range?

user007486
user007486 Member
in Magic ETL

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

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user007486
    user007486 Member

    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
    jaeW_at_Onyx Coach

    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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories