Join with Between Clause in Magic ETL
I am working on converting some Redshift dataflows to Magic ETL 2.0 dataflows. I have a join statement in Redshift that looks like this:
SELECT d."province_state" , c."Category" as SeverityRelativityCategory , n."Category" as SeverityRelativityCategoryMinusNY FROM "ihme_covid_19_population_death_shares" d JOIN "severity_relativity_categories" c on d."SeverityRelativity" BETWEEN c."MinAmt" AND c."MaxAmt" JOIN "severity_relativity_categories" n on d."SeverityRelativityMinusNY"BETWEEN n."MinAmt" AND n."MaxAmt"
I'm not seeing an easy way to handle the BETWEEN clause using the JOIN tile in Magic ETL 2.0. How can I accomplish this?
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.
Best Answer
-
Magic ETL doesn't support a conditional join, only a straight columnar join.
The option you can do is perform a Cartesian join and then user a filter tile after the fact. To do that, add a constant of 1 to both of your datasets and then join on that column. The Cartesian join causes the number of records to explode which you then have to filter through so it's the most efficient but should get you what you want.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Answers
-
Magic ETL doesn't support a conditional join, only a straight columnar join.
The option you can do is perform a Cartesian join and then user a filter tile after the fact. To do that, add a constant of 1 to both of your datasets and then join on that column. The Cartesian join causes the number of records to explode which you then have to filter through so it's the most efficient but should get you what you want.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
Thanks @GrantSmith ! That did the trick. Here is a visual of what it ended up looking like for anyone else who needs to do the same thing.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
@MarkSnodgrass a little late for the party, but here's an alternative to doing a full on cartesian product.
i'm assuming your dataset is relatively small so multiplying Table A times the number of rows in Table B isn't THAT big a deal.
For a solution that scales (in certain use cases), you can duplicate table C and N by the row by the number of rows between Min and Max Amount
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 -
@jaeW_at_Onyx 's video saved my butt because I've gone DEEP down the rabbit hole with Magic v2 and ran face first into needing a BETWEEN JOIN.
That trick will absolutely work for me .... for now. I add 48 rows every day. So, a year from now ... sum=[n*(n+1)]/2
153,483,960 rows :/
0 -
@SeanPT thaaaaats gonna get expensive. do you want to post a screenshot of what you've currently got and describe the JOIN?
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"0 -
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"0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 96 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 705 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive