Trying to convert Month of the Year into Date with Magic ETL

hecl3
hecl3 Member
in Magic ETL

Basically the title. I am using a dataset that pulls Month of the Year instead of Date, as the User count with the Date metric isn't accurate. However I have run into issues trying to convert Month of the Year into a date format. Here is the original format:

image.png

As you can see, each month is numbered from 1-12. I used Value Mapper to convert the numbers to more of a date format, see here:

image.png

I thought from there I should be able to convert the integer strings to a Date. However I am having difficulties with that part. I'm not sure if I'm using the wrong formulas in the Formula tile. I tried DATE_FORMAT(Month of the year, '%M %d, %Y') but it didn't work in Magic ETL, though it did in Beast Mode. The Date Operations tile wasn't working for me either. Would really appreciate some guidance on this.

Tagged:

Best Answer

Answers

  • GrantSmith
    GrantSmith Coach

    Your format string isn't quite correct. You need something like

    DATE_FORMAT(`Month of the year`, '%Y%m%d')

    One concern though is that the months are set to a specific year - is this intended or will other years have the same month?

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

    Ok, I tried that and it looks like I'll need to convert it from an integer to a date first. This was the error message I got:

    image.png

  • MarkSnodgrass
    MarkSnodgrass Coach

    You can use the formula tile and use the concat and date functions to build your date and make it be recognized as a date field.

    Here is an example where the date will always be in 2018

    DATE(CONCAT(`Month of the year`,'/1/2018'))

    Here is another example where it will extract the year from the current date

    DATE(CONCAT(`Month of the year`,'/1/',YEAR(CURRENT_DATE())))

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • DashboardDude
    DashboardDude Coach
    edited March 12 Answer ✓

    @hecl3

    Here's my video on how to do this using a CAST function:

    https://www.youtube.com/watch?v=OPKuKCTRxH0

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • hecl3
    hecl3 Member

    Thanks everyone for your help! I was able to get it up and running

Categories