Generate Date Range Column from Two Date Columns

lvtrujillot
lvtrujillot Member
in Magic ETL

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

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    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!**

Answers

  • GrantSmith
    GrantSmith Coach
    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!**
  • lvtrujillot
    lvtrujillot Member

    @GrantSmith That's exactly what I need. Thank you!

Categories