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.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 282 Workbench
- 3 Cloud Amplifier
- 4 Federated
- 2.8K Transform
- 86 SQL DataFlows
- 548 Datasets
- 2.2K Magic ETL
- 3.2K Visualize
- 2.3K Charting
- 542 Beast Mode
- App Studio
- 26 Variables
- 566 Automate
- 134 Apps
- 411 APIs & Domo Developer
- 21 Workflows
- DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 344 Distribute
- 86 Domo Everywhere
- 257 Scheduled Reports
- 1 Software Integrations
- 85 Manage
- 84 Governance & Security
- 6 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 81 Community Announcements
- 4.8K Archive