Joining 2 datasets based on between condition.

user01706
user01706 Member
in Magic ETL

Hello,

I have two data sets that i would like to join based on a between condition. The "Join data" available in the ETL shows only inner & outer joins. Where can i do custom joins?

Here is the detailed scenario.

dataset A has 2 columns - clicks, date

dataset B has ID, start date, end date.

I need an output  with B.ID, sum(A.clicks) based on A.date between B.start date and B.end date.

Each ID has separate start and end dates that don't overlap with other IDs.

Thanks in advance!

 

 

Best Answer

  • AS
    AS Coach
    Answer ✓

    Hi Srujana

     

    You'll want to use a SQL dataflow for this instead.  ETL only allows limited join options, like you stated.

    Aaron
    MajorDomo @ Merit Medical

    **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"

Answers

  • AS
    AS Coach
    Answer ✓

    Hi Srujana

     

    You'll want to use a SQL dataflow for this instead.  ETL only allows limited join options, like you stated.

    Aaron
    MajorDomo @ Merit Medical

    **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"
  • ST_Superman
    ST_Superman Domo Employee

    If you would like help with the MySQL code, feel free to ask.  But @AS is right, I'm not aware of a way to do this inside an ETL at the moment.  I have heard tell of some additions to ETL that will allow you to write in Python or R code directly to the ETL.  But for now, I think MySQL is your solution.

  • AS
    AS Coach

    Eventually we'll probably see SQL transforms in Magic ETL (so the rumor goes), which means we could have the best of both worlds. I have my fingers crossed, my hopes high, and my expectations in check.

    Aaron
    MajorDomo @ Merit Medical

    **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"
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    Product Feedback / IDEA added here -- https://dojo.domo.com/main/discussion/55706/join-on-like-and-between-in-magic-2-0/p1?new=1

    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