How to Make a Weekly date range into a Daily Date?

WorldWarHulk
WorldWarHulk Member
in Magic ETL

Hello Domo Dojo Masters -

Good Day!


We have data from salesforce that only has a start of the week and end of the week date columns>>>

image.png


How can I make it so that we can have a daily date?

image.png


Tagged:

Best Answer

  • BenSchein
    BenSchein Admin
    Answer ✓

    @WorldWarHulk I have done this with similar data from Safegraph. You should be able to do this in a Magic ETL. First step is to use the Unpivot command. To get the data to be something like thins:

    Start Week, End Week, Dayof Week, Value

    With Day of Week being the column name Sunday to Saturday.

    Then you can do a formula to get the actual date. Something like this

    case

    when DayOfWeek='Monday' then dateadd(startweek,1)

    when DayOfWeek='Tuesday' then dateadd(startweek,1)

    You will need to check the formula syntax in the formula editor in magic. but hopefully you get the idea here.

Answers

  • BenSchein
    BenSchein Admin
    Answer ✓

    @WorldWarHulk I have done this with similar data from Safegraph. You should be able to do this in a Magic ETL. First step is to use the Unpivot command. To get the data to be something like thins:

    Start Week, End Week, Dayof Week, Value

    With Day of Week being the column name Sunday to Saturday.

    Then you can do a formula to get the actual date. Something like this

    case

    when DayOfWeek='Monday' then dateadd(startweek,1)

    when DayOfWeek='Tuesday' then dateadd(startweek,1)

    You will need to check the formula syntax in the formula editor in magic. but hopefully you get the idea here.

Categories