Generate Date Range Column from Two Date Columns
Hi all !
Im trying to generate a date column with a range of dates from two columns:
Table I have (date format (mm/dd/yyyy) )
Id start_date end_date field1 1 01/01/2019 07/01/2019 text1 1 07/01/2019 12/01/2019 text2 2 01/01/2019 02/01/2019 text3
The idea is to generate a list of dates between start and end date with an interval of 1 month.
Output Table :
Id date field1 1 01/01/2019 text1 1 02/01/2019 text1 1 03/01/2019 text1 1 04/01/2019 text1 1 05/01/2019 text1 1 06/01/2019 text1 1 07/01/2019 text2 1 10/01/2019 text2 1 11/01/2019 text2 1 12/01/2019 text2 2 01/01/2019 text3 2 02/01/2019 text2
Any idea how could I get this ?
Appreciate the help! Thanks
Best Answer
-
I just posted something for a similar question but you can use the calendar.csv dataset from the Domo Dimensions connector.
Then you can use MySQL to get your desired output (MagicETL doesn't support conditional joins like this directly. You'd have to do a cartesian join [add constant column to both - say a value of 1 - and then join both datasets on that value then use a filter tile to filter your dataset down. This would exponentially increase the amount of records MagicETL would have to process and isn't as straight forward as MySQL in this case)
select `Id`, `dt` as date, `field1` FROM `calendar_dates` as cd JOIN `my_dataset` ds on cd.dt BETWEEN ds.`start_date` and ds.`end_date` and cd.d = 1
cd.d=1
just tells it to return the record for the first day of the month since you're wanting to do it on a monthly basis and your data appears to use the first day to represent each month.**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
I just posted something for a similar question but you can use the calendar.csv dataset from the Domo Dimensions connector.
Then you can use MySQL to get your desired output (MagicETL doesn't support conditional joins like this directly. You'd have to do a cartesian join [add constant column to both - say a value of 1 - and then join both datasets on that value then use a filter tile to filter your dataset down. This would exponentially increase the amount of records MagicETL would have to process and isn't as straight forward as MySQL in this case)
select `Id`, `dt` as date, `field1` FROM `calendar_dates` as cd JOIN `my_dataset` ds on cd.dt BETWEEN ds.`start_date` and ds.`end_date` and cd.d = 1
cd.d=1
just tells it to return the record for the first day of the month since you're wanting to do it on a monthly basis and your data appears to use the first day to represent each month.**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
@GrantSmith That's exactly what I need. Thank you!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive