ETL - Get/Remove Values after First Space
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
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Thanks @MarkSnodgrass! That Easy.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 693 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive