Data Structure Issue

ArborRose

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.

MarkSnodgrass

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.

GrantSmith

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.

MarkSnodgrass

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.

ArborRose

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

MarkSnodgrass

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.

ArborRose

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 .

MarkSnodgrass

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

https://youtu.be/QIuuXjiH1JY