Joining 2 datasets based on between condition.
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
-
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"0
Answers
-
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"0 -
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.” -Superman0 -
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"1 -
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"1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive