Data Structure Issue

Options

Short version: how do I check if a field is a valid date?

Long version:

Using the NetSuite connector, our data has a field called Period.

The connector does not allow me to define the data structure. I tried admin » toolkit » schema management. It doesn't hold. If I force the dataset field to a string, the next run it puts it back as a date.

When its not the end of the year, the data comes through as Jan 2024, Feb 2024, etc. At the end of the year it will come through with some data having "Adjust 2024"….which causes Domo to move to a string. Grrr.

I wish Domo would stop guessing at data structures, it always gets them wrong!

I need some logic to test whether Period is a date. But I can't find an equivalent Domo command to IsDate or ParseDate, etc. I need to check if the field is a date and if it is a date convert it using DATE_FORMAT(Period,'%b %y'). If it's not a date, leave it alone.

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

Best Answer

  • MarkSnodgrass
    Answer ✓
    Options

    TRY_CAST is only available in the Magic ETL formula tile. It won't work in the Analyzer calculated field. There are a handful of functions that only work in the ETL formula tile and this is one of them.

    **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.

Answers

  • GrantSmith
    Options

    You can attempt to do your DATE_FORMAT call but encompass it inside of an IFERROR call to filter out the rows where you're note able to convert to a date with your format.

    IFERROR(EXPR1,EXPR2)
    If the evaluation of expr1 does not produce an error, IFERROR() returns expr1; otherwise it returns expr2.
    

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

    In the ETL, you can use the formula tile and use TRY_CAST(myfield AS DATE). If it is a valid date, it will become a date value. If it isn't valid, it will become null. You can use this as a standalone formula and it will create a date field that will have dates and null values. Or you could use it as part of a CASE statement where you can do other things if it is null.

    **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.
  • ArborRose
    Options

    Testing it on a calculated field in a card, it's rejecting TRY_CAST as a valid command.

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

  • MarkSnodgrass
    Answer ✓
    Options

    TRY_CAST is only available in the Magic ETL formula tile. It won't work in the Analyzer calculated field. There are a handful of functions that only work in the ETL formula tile and this is one of them.

    **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.
  • ArborRose
    Options

    Note for anyone reading this thread. I believe Grant's solution will also work. But I did not test it. As Mark mentioned, TRY_CAST does appear to be working in the ETL. And that's where I want it happening so my issue is solved.

    Thank you both @MarkSnodgrass , @GrantSmith .

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

  • MarkSnodgrass
    Options

    For those dealing with bad data, here is a video that goes over various ways to clean it up:

    **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.