Joining 2 datasets based on between condition.

user01706
user01706
in Dataflows

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 🔵
    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

  • ST_-Superman-_
    ST_-Superman-_ 🟤

    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.


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
  • AS
    AS 🔵

    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"

Categories