Using Magic ETL to extract a substring using Regex and excluding remaining strings
Hello!
I know variations of this question have been asked in the forums but I haven't been able to find a solution that works for me.
I have a column Project Name
that contains Projects out of Financial Force that may contain an At-Risk ticket number, which is formatted as: AR-00000 i.e. literally "AR-" or "AR - " followed by a 5 digit number. For example: AR-12345 Pinegrove Building Supply and Contractors with Consultation Hours. I want to preserve the AR number and delete the remaining strings. The Regex I've written and played with is \b(AR-\d{5})\b
I have tried using MagicETL's "Formula" and "Replace Text" (with the Use Regex flag enabled) tiles but have been unable to preserve the AR ticket number while also dropping the remaining text.
One of my configuration attempts is as follows:
Replace Text Tile
Configuration 1 (Column to search): Project Name
Config 2 (search term with Use Regex enabled): .*\b(AR-\d{5})\b
Config 3 (replace term): Empty string
Result (unchanged): AR-12345 Pinegrove Building Supply and Contractors with Consultation Hours
Can you please advise a solution?
Best Answer
-
You'd need to do it in a formula tile instead of the replace text tile with something like this:
CASE WHEN REGEXP_REPLACE(`Project Name`,'^.*\b(AR-\d{5})\b.*$','$1') <> `Project Name` THEN REGEXP_REPLACE(`Project Name`,'^.*\b(AR-\d{5})\b.*$','$1') END
or alternatively:
CASE WHEN REGEXP_LIKE(`Project Name`,'^.*\b(AR-\d{5})\b.*$','$1') THEN REGEXP_REPLACE(`Project Name`,'^.*\b(AR-\d{5})\b.*$','$1') END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
You'd need to do a replacement variable and tell it to ignore the rest of the string
Try changing your regex to:
^.*\b(AR-\d{5})\b.*$
Then in your replace term use $1 to replace it with your first match group.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
John Le
You're only one dashboard away.
Click here for more video solutions:
1 -
Thanks Grant. I updated the following:
Replace Text tile:
Config (Column to search):Project Name
Config 2 (search term with Use Regex enabled): ^.\b(AR-\d{5})\b.$
Config 3 (replace term): $1This results in:
For rows that do contain an AR number, the result is just the AR number, which is expected. For rows that do not contain an AR number, the
Project Name
value is preserved but I want it to return null or blank or empty.Add Formula tile:
Config (Name output column):AR num
Config 2 (formula):REGEXP_REPLACE(`Project Name`,'^.\b(AR-\d{5})\b.*$','$1')
This results in:
A new AR num column, which is fine. For rows that do contain an AR number, the result is just the AR number, which is expected. For rows that do not contain an AR number, the
Project Name
value is preserved but I want it to return null or blank or empty.Is there a way to return
null
when the "parent string" doesn't contain a match?0 -
You'd need to do it in a formula tile instead of the replace text tile with something like this:
CASE WHEN REGEXP_REPLACE(`Project Name`,'^.*\b(AR-\d{5})\b.*$','$1') <> `Project Name` THEN REGEXP_REPLACE(`Project Name`,'^.*\b(AR-\d{5})\b.*$','$1') END
or alternatively:
CASE WHEN REGEXP_LIKE(`Project Name`,'^.*\b(AR-\d{5})\b.*$','$1') THEN REGEXP_REPLACE(`Project Name`,'^.*\b(AR-\d{5})\b.*$','$1') END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks @DashboardDude for the workaround. This made total sense as an alternative solution! I got stuck in a box and, due to stubbornness, didn't consider a non-Regex solution haha.
1 -
@NDoyle ,
Glad it helped! I know the stubbornness route well so wanted to save you a headache.
Please mark my solution as helpful when you can
John Le
You're only one dashboard away.
Click here for more video solutions:
1
Categories
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 970 Connectors
- 259 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 76 SQL DataFlows
- 502 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 379 Beast Mode
- 21 Variables
- 487 Automate
- 104 Apps
- 379 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 319 Distribute
- 67 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 64 Community Announcements
- 4.8K Archive