Date format question

A2024
A2024 Member
in Magic ETL

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.

Tagged:

Best Answer

  • jimsteph
    jimsteph Contributor
    edited June 2023 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 case DATE(`mydate`) — is successful you get the results of the first argument. If it doesn't, the IFERROR() function catches that and substitutes the second argument, in this case NULL. If you really need error results to be zero you'd just wrap everything in another IF() function and test for NULL.

    To verify this worked I created a webform and entered four values in it:

    image.png

    I then created a quick ETL:

    image.png

    The end result is:

    image.png

Answers

  • GrantSmith
    GrantSmith Coach
    edited June 2023

    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!**
  • A2024
    A2024 Member

    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"

  • MichelleH
    MichelleH Coach

    @Aag2023 What do the valid date values look like in Domo? You will likely need to use a STR_TO_DATE() function to parse it into a date field yourself

  • marcel_luthi
    marcel_luthi Coach

    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.

  • jimsteph
    jimsteph Contributor
    edited June 2023 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 case DATE(`mydate`) — is successful you get the results of the first argument. If it doesn't, the IFERROR() function catches that and substitutes the second argument, in this case NULL. If you really need error results to be zero you'd just wrap everything in another IF() function and test for NULL.

    To verify this worked I created a webform and entered four values in it:

    image.png

    I then created a quick ETL:

    image.png

    The end result is:

    image.png

Categories