Magic ETL

Magic ETL

Remove text from a column

Hi all, I'm trying to figure out the easiest way to create a new column with slightly filtered strings.

My "Team" column contains strings such as:

  • Team Name A - EMEA
  • Team Name A - AMERICAS
  • Team Name A - APAC
  • Team Name B - EMEA
  • Team Name B - AMERICAS
  • Team Name B - GLOBAL

I want to create a new column, "Simplified Team" that contains the same text, but with the EMEA, AMERICAS, APAC, GLOBAL parts of the string removed.

Note: Not all teams have the region appended to them.

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

Best Answer

  • Coach
    Answer ✓

    You can use a formula tile in Magic ETL to do a regular expression replacement

    1. REGEXP_REPLACE(`Team`, '( - EMEA)|( - AMERICAS)|( - GLOBAL)|( - APAC)', '')


    Alternatively you can chain REPLACE functions together in a Beast Mode:

    1. REPLACE(REPLACE(REPLACE(REPLACE(`Team`, ' - EMEA', ''), '- AMERICAS', ''), ' - GLOBAL', ''), '- APAC', '')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • If your format is split by a hyphen you could use SPLIT_PART to get the first part of your string:

    1. SPLIT_PART(`Team`, ' - ', 1)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • That's getting me closer, but the team names have a bazillion hyphens in them. 😥 E.g. "Engineering - Controls - Americas" or "Marketing - Communication - Global" or "Super long team name - with more hyphens - than a team name needs - Americas"

  • So you just want to drop the last section after the last hyphen?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Ish. Essentially, I want a new "Simplified Team" column that copies "Team" and then either truncates the following strings, or does a search and replace of the strings below and leaves nothing behind.

    • " - EMEA"
    • " - AMERICAS"
    • " - GLOBAL"
    • " - APAC"
  • Coach
    Answer ✓

    You can use a formula tile in Magic ETL to do a regular expression replacement

    1. REGEXP_REPLACE(`Team`, '( - EMEA)|( - AMERICAS)|( - GLOBAL)|( - APAC)', '')


    Alternatively you can chain REPLACE functions together in a Beast Mode:

    1. REPLACE(REPLACE(REPLACE(REPLACE(`Team`, ' - EMEA', ''), '- AMERICAS', ''), ' - GLOBAL', ''), '- APAC', '')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

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