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

Golfoholic · 2021-07-28T18:58:54+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

REGEX

Date

Magic ETL

Data Flows

Accepted answers

MarkSnodgrass

@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.

All comments

GrantSmith

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.

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

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.

Golfoholic

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

GrantSmith

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

MarkSnodgrass