Skip to content

    • Magic ETL

    Magic ETL

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

    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

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

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



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

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

      and wrap the TRIM command in there for spaces

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

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

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



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

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

      and wrap the TRIM command in there for spaces

      1. 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! **

    • Member

      Thank you! The command worked great.

    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