Skip to content

    • Magic ETL

    Magic ETL

    How do I fix a string error with a date?

    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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • 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

    • 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:

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

    Answers

    • 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

    • 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
    • Coach
      edited October 2024

      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

    • 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
    • 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

    • 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:

      1. TRY_CAST(`Best Few Date` as DATE())
    • 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!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories