How to delete portion of text in a column in the ETL?

Golfoholic
Golfoholic
in Magic ETL

I have a date column that is in this format:

image.png

The format of the date is fine, but what I am trying to do is get rid of everything including the 'T' and to the right, so that all that is left would be 2021-06-27. If a solution within the ETL is possible that would be great.

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @user048760 in Magic ETL, you can use the String Operations tile and choose Right/Left Substring, select your column, enter 10 for the substring length and choose Left for side to start from. This will remove everything from the T and later, leaving just the date.

    Next, drag in the the Set Column Type tile and connect it to your String Operations tile. Select your newly created column from the String Operations tile and choose Date for the type. This will convert it to a date data type, which will make it easier to work with in the Analyzer.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user048760

    Was this field imported as a timestamp or is it configured as a string? You can utilize the Set Column Type tile in Magic ETL 1.0 or the Alter Columns in Magic ETL 2.0 to force the data type to be a Date.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Golfoholic
    Golfoholic

    it is configured as string. Illustrates the date someone is scheduled to move-out. the minutes/seconds is kind of pointless. Is implemented in the report on the side where this data comes from prior to getting loaded in to Domo.

  • GrantSmith
    GrantSmith Coach

    In that case if Domo is unable to convert the string directly to a date you may need to convert it to a timestamp first before you then convert it to a date.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Golfoholic
    Golfoholic

    @GrantSmith is that also possible within the ETL, or would it have to take place in the system where this data originates?

  • GrantSmith
    GrantSmith Coach

    Those are actual tiles within the Magic ETL platform. You can use those in your Magic ETL to convert your string to a date.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @user048760 in Magic ETL, you can use the String Operations tile and choose Right/Left Substring, select your column, enter 10 for the substring length and choose Left for side to start from. This will remove everything from the T and later, leaving just the date.

    Next, drag in the the Set Column Type tile and connect it to your String Operations tile. Select your newly created column from the String Operations tile and choose Date for the type. This will convert it to a date data type, which will make it easier to work with in the Analyzer.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories