Date format question
Hello all,
I have a snowflake i/p data source. One of a column is a date field and it showing up as floating decimal in Domo. This field has value -3 (even in data source) if it is a null else it is a date..
As this is floating decimal type in domo, under configuration I changed to Date type. When I try to join with another table, I get an error. It says "Failed to parse -3 from this date field..". So looks like it is failing due to -3 values.
Can you help me with the syntax on how to convert this to 0 or null if -3 is found in date field. I believe once I do this prior to join and then join should work.
Appreciate your help.
Best Answer
-
You may want to wrap your attempted conversion in the IFERROR() function. You'd do something like
IFERROR(DATE(`mydate`), NULL).
If the first argument in the
IFERROR()
function is successful — in this caseDATE(`mydate`)
— is successful you get the results of the first argument. If it doesn't, theIFERROR()
function catches that and substitutes the second argument, in this caseNULL
. If you really need error results to be zero you'd just wrap everything in another IF()
function and test forNULL
.To verify this worked I created a webform and entered four values in it:
I then created a quick ETL:
The end result is:
0
Answers
-
Use a Magic ETL with a formula tile to make the value null if it's -3:
case when `field` <> -3 then `field` end
You can then feed that into an alter column tile to change the type
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank @GrantSmith . I tried doing the above. I first reset the type in configuration so it is back to Floating decimal. Then after formula tile I placed Alter tile to convert to Date type.
Now I get an error after the join. The error says "An error occurred in Alter Columns" Column of type Floating Decimal cannot be converted to type Date"
0 -
Agree with @MichelleH, first thing is to know what valid values look like in Domo, as there is no intrinsic conversion from Float to Date. Since the type is returned as float, that either means most of your entries were -3 which is how Domo decided to use that type for that field (not so great news and perhaps you'd need to research why -3 is being used in Snowflake for these) or that Snowflake returns the date as a number, perhaps as Epoch, in which case you'll need to convert it using FROM_UNIXTIME() and a base 10 factor.
1 -
You may want to wrap your attempted conversion in the IFERROR() function. You'd do something like
IFERROR(DATE(`mydate`), NULL).
If the first argument in the
IFERROR()
function is successful — in this caseDATE(`mydate`)
— is successful you get the results of the first argument. If it doesn't, theIFERROR()
function catches that and substitutes the second argument, in this caseNULL
. If you really need error results to be zero you'd just wrap everything in another IF()
function and test forNULL
.To verify this worked I created a webform and entered four values in it:
I then created a quick ETL:
The end result is:
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive