Magic ETL

Magic ETL

Parsing Dates with Different Formats

Does anyone have any guidance for using a formula/function in a beast mode or using the regex functionality to ensure Domo recognizes all of the dates below as the same?

  • 5/12/2022
  • 5/12/22
  • 2022-05-12
  • 5.12.2022
  • 5.12.22
Tagged:

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Answers

  • You can wrap a couple REPLACE functions around your field that replaces the hyphens and periods with slashes followed by the DATE function to format these as date. It would look something like this:

    1. DATE(
    2.  REPLACE(
    3.  REPLACE(`String`,'-','/')
    4.   ,'.','/')
    5.  )
    **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.
  • Thanks - that worked for most of the entries - but now all the ones that had 22 as the year vs. 2022 still aren't showing up well. So I'm left with the remaining formats:

    • 22-5-20
    • 22-2022-05

    I thought about doing a replace on 22 to 2022 but I'm afraid that will impact 5/22/2022 items.

  • Can you post your formula? Wrapping the DATE function around everything should normalize the years.

    **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.
  • DATE(

     REPLACE(

     REPLACE(`Interview Scheduled Date`,'-','/')

     ,'.','/')

     )

  • Actually the dataset errored out - let me investigate further.

  • It keeps erroring out noting "Failed to convert value '005/27/22' from type 'String' to type 'Date'."


    I then added to your formula

    DATE(

     replace(REPLACE(

     REPLACE(`Interview Scheduled Date`,'/','-')

     ,'.','-'),'00','0')

     )

    But I got this result, "Failed to convert value '05-27-22' from type 'String' to type 'Date'."

  • See how close this gets you:

    1. DATE(
    2. REPLACE(
    3. REPLACE(
    4. REPLACE(`Interview Scheduled Date`,'00','')
    5.  ,'-','/')
    6.  ,'.','/')
    7.  )

    This still might not work for 100% of your data because it seems like you have rows with 2 digit years in different spots and Domo won't know if that is a year or a day. You might need to try and thinking about incorporating a case statement as well and performing different logic based on certain conditions.

    **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.
  • Got it - I will look at the case logic - from your last note - I got the following error:

    "Failed to convert value '22/04/18' from type 'String' to type 'Date'."

  • I think the logic is helpful enough - I think I will have to add some data validation to the front end.

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In