Data Structure Issue
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
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Answers
-
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!**0 -
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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! **0 -
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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! **1 -
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive