Holidays between two dates
Dataset A contains date_start and date_end.
Dataset B is a calendar with a field called is_company_holiday.
I need to figure out a way (in Magic ETL 2.0) to create a value in Dataset A that tells me the number of holidays between date_start and date_end.
Anyone got any good suggestions?
Thanks to previous threads that have helped me get this far. @MarkSnodgrass in particular has been helpful. Thank you.
Comments
-
Since you can't do conditional joins within Magic ETL 2.0 what you can do is add a constant to both datasets in your ETL and call it Join Column with a value of 1. Then do a join on both datasets on the join column. Then feed that into a filter tile to filter where company_holiday_date is BETWEEN your date_start and date_end. Then you can do a group by and count the number of records you have, joining that metric back to your original dataset A based on the start and end dates to record the number of holidays between the two dates.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 -
@GrantSmith Thank you for this quick resolution.
This makes complete sense, thank you for helping me get the last little bit of it.
I could see how, if Dataset A had large amounts of historical in it, the join in Magic could produce 100s of millions of rows fairly easily, lengthening your data run time. However, Adrenaline is pretty awesome and our historical only goes back a few months (for now). So our run time only increased by 1.5min.
Anyway, problem solved and thank you again!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 708 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
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive