What is the best why to change the format of data in a cell?

kim_barragan0126
kim_barragan0126 Member
in Magic ETL

I have a data set that brings in names under the column BDM Name with FY 2024 at the end of the name, see below screenshot. What is the best way to remove the "FY2024?" I have about 8 BDM names I need to change. Should I use a replace text tile in ETL?

image.png

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    You can use a replace text tile in an ETL to remove the known value, "FY2024". Or a command like this if it's static:

    TRIM(SUBSTRING(`BDM Name`, 1, LENGTH(`BDM Name`) - LENGTH('FY2024')))



    Or get a be more complicated using a regular expression in a formula:

    REGEXP_REPLACE(`BDM Name`, 'FY\\d{4}', '')

    and wrap the TRIM command in there for spaces

    TRIM(REGEXP_REPLACE(`BDM Name`, 'FY\\d{4}', ''))

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

Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    You can use a replace text tile in an ETL to remove the known value, "FY2024". Or a command like this if it's static:

    TRIM(SUBSTRING(`BDM Name`, 1, LENGTH(`BDM Name`) - LENGTH('FY2024')))



    Or get a be more complicated using a regular expression in a formula:

    REGEXP_REPLACE(`BDM Name`, 'FY\\d{4}', '')

    and wrap the TRIM command in there for spaces

    TRIM(REGEXP_REPLACE(`BDM Name`, 'FY\\d{4}', ''))

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

  • kim_barragan0126
    kim_barragan0126 Member

    Thank you! The command worked great.

Categories