ETL - Get/Remove Values after First Space

TimWells
TimWells Member
in Magic ETL

Hi All,

I am trying to separate the string's below to form two different columns.

So my current column containing all the value is called 'Type' and i want to separate it to be two columns 'Medium' and 'Campaign'

'paid Car_Loans' should be 'paid' and 'Car Loans'

'callext Car Loans 2021' should be 'callext' and 'Car Loans 2021'

Is there anyway to do this in an ETL?

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    edited July 2021 Answer ✓

    Yes, you can do this very easily in Magic ETL using the Split Columns tile. Just drag the Split Columns tile in, select that column you wish to split, choose Whitespace for what to split on, place a check next to "keep extra splits", then give a name to the column that will hold the values before the space, click add column to give a name to the column that will hold the values after the space.

    If you have Magic ETL 2.0, you can use the Add Formula tile and use the SPLIT_PART function to separate them like this:

    First column: SPLIT_PART(`fieldname`,' ',1)

    Second column: TRIM(SUBSTRING(`fieldname`,INSTR(`fieldname`,' '),LENGTH(`fieldname`)))

    The second one is a bit more involved because you want everything after the space, not just to the next space.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    edited July 2021 Answer ✓

    Yes, you can do this very easily in Magic ETL using the Split Columns tile. Just drag the Split Columns tile in, select that column you wish to split, choose Whitespace for what to split on, place a check next to "keep extra splits", then give a name to the column that will hold the values before the space, click add column to give a name to the column that will hold the values after the space.

    If you have Magic ETL 2.0, you can use the Add Formula tile and use the SPLIT_PART function to separate them like this:

    First column: SPLIT_PART(`fieldname`,' ',1)

    Second column: TRIM(SUBSTRING(`fieldname`,INSTR(`fieldname`,' '),LENGTH(`fieldname`)))

    The second one is a bit more involved because you want everything after the space, not just to the next space.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • TimWells
    TimWells Member

    Thanks @MarkSnodgrass! That Easy.

Categories