Best approach to map day of date in bulk

cthtcc
cthtcc Member
in Magic ETL

We need to map the Date field so that every date value is the first of the month. The value mapper tile can do this one at a time, but we need to do this in bulk. Can anyone recommend the best approach using a function?

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    `Date` - INTERVAL (DATOFMONTH(`Date`) - 1) DAY
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Should be DAYOFMONTH

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    `Date` - INTERVAL (DATOFMONTH(`Date`) - 1) DAY
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • cthtcc
    cthtcc Member

    I'm getting an 'unknown function:DATOFMONTH' error in my ETL preview on this @GrantSmith I tried `Date` - INTERVAL (DATEOFMONTH(`Date`) - 1) DAY in case there was a typo on DATE, but still get 'unknown function'

    As a workaround I used Date Function to extract Month of Date and the function below

    CASE 

    WHEN MONTH(`Date`) = 1 then '1/1/2022'

    WHEN MONTH(`Date`) = 2 then '2/1/2022'

    WHEN MONTH(`Date`) = 3 then '3/1/2022'

    WHEN MONTH(`Date`) = 4 then '4/1/2022'

    WHEN MONTH(`Date`) = 5 then '5/1/2022'

    WHEN MONTH(`Date`) = 6 then '6/1/2022'

    WHEN MONTH(`Date`) = 7 then '7/1/2022'

    WHEN MONTH(`Date`) = 8 then '8/1/2022'

    WHEN MONTH(`Date`) = 9 then '9/1/2022'

    WHEN MONTH(`Date`) = 10 then '10/1/2022'

    WHEN MONTH(`Date`) = 11 then '11/1/2022'

    WHEN MONTH(`Date`) = 12 then '12/1/2022'

    end

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Should be DAYOFMONTH

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories