Joining 2 datasets based on between condition.
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!