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

user048760
user048760 βšͺ️

I have a date column that is in this format:

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.

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟀
    Accepted 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.

Answers

  • GrantSmith
    GrantSmith Indiana πŸ”΄

    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.

  • user048760
    user048760 βšͺ️

    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 Indiana πŸ”΄

    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.

  • user048760
    user048760 βšͺ️

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

  • GrantSmith
    GrantSmith Indiana πŸ”΄

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