How do I fix a string error with a date?

kim_barragan0126
kim_barragan0126 Member
in Magic ETL

I am getting the below error and now sure how to fix. I run this data weekly, so I am assuming I have bad data somewhere.

image.png

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Where is your data coming from? If you can edit the value in the source system directly, you could change it from '0/00/2024' to a proper date. If that isn't an option, you could fix the data using a formula tile:

    CASE WHEN `DateField` = '0/00/2024' THEN '12/31/2024' ELSE `DateField` END

    If I solved your problem, please select "yes" above

  • MichelleH
    MichelleH Coach
    Answer ✓

    @kim_barragan0126 Based on your screenshot, it looks like your source data is from Excel. Likely what's happening is that you have a typo in one of your date columns. Since you only have 5 date columns, I'd suggest opening the filters of each date column to look for invalid date values. If a value is invalid, Excel cannot group it under a particular month/year with expandable "+" signs, which makes it easy to filter to bad values and correct them in the sheet before uploading.

    image.png

    Alternatively, you can try converting your datatypes in the ETL using the TRY_CAST function in a formula tile instead of the Alter columns. When using TRY_CAST, if it encounters a value that it cannot convert to a different datatype then the output for that row will be NULL instead of causing the dataflow to fail. Your formulas should look like this:

    TRY_CAST(`Best Few Date` as DATE())

Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Where is your data coming from? If you can edit the value in the source system directly, you could change it from '0/00/2024' to a proper date. If that isn't an option, you could fix the data using a formula tile:

    CASE WHEN `DateField` = '0/00/2024' THEN '12/31/2024' ELSE `DateField` END

    If I solved your problem, please select "yes" above

  • kim_barragan0126
    kim_barragan0126 Member

    I have five date fields so I am not sure the one that has the issue. All the dates are noted as the below.

    Is there a way to determine which field is causing the issue?

    image.png

  • ColemenWilson
    ColemenWilson Coach
    edited October 28

    If you can change the type, the 2012-03-14 format is preferred.

    In Domo, navigate to the dataset and then inspect the schema. You'll be able to see the data type for each field. If you see a field that should be a date, but shows 'abc Text' Then that is likely where your issue is.

    Screenshot 2024-10-28 at 9.34.30 AM.png

    If I solved your problem, please select "yes" above

  • kim_barragan0126
    kim_barragan0126 Member

    It shows Date in my data, but string when I run it through ETL. I tried using a Alter tile and change to Date, but that causes an error. I also tried changing the file itself to text and date and both issues. Maybe I need support ticket?

    image.png
    image.png

  • ColemenWilson
    ColemenWilson Coach

    Huh. So as the input it is a date, but when using it in the ETL it is converting it to a text and not allowing you to convert it back to date? Seems like you have identified which date field is causing the issue, did you find the value '0/00/2024' in that column?

    If I solved your problem, please select "yes" above

  • MichelleH
    MichelleH Coach
    Answer ✓

    @kim_barragan0126 Based on your screenshot, it looks like your source data is from Excel. Likely what's happening is that you have a typo in one of your date columns. Since you only have 5 date columns, I'd suggest opening the filters of each date column to look for invalid date values. If a value is invalid, Excel cannot group it under a particular month/year with expandable "+" signs, which makes it easy to filter to bad values and correct them in the sheet before uploading.

    image.png

    Alternatively, you can try converting your datatypes in the ETL using the TRY_CAST function in a formula tile instead of the Alter columns. When using TRY_CAST, if it encounters a value that it cannot convert to a different datatype then the output for that row will be NULL instead of causing the dataflow to fail. Your formulas should look like this:

    TRY_CAST(`Best Few Date` as DATE())

  • kim_barragan0126
    kim_barragan0126 Member

    Thank you all! I have fixed the issue, not sure how I fixed it, but it is good now. I will go back now and see what steps worked. I appreciate all the help as it did help me determine what the issue was, and how to research it. Thank you!

Categories