DOMO Regex Extraction ETL
I'm looking to extract a work order number which contains the letter W followed by 15 digits (Ex. W571191476628566).
The order number lies within a string containing notes content that has been exported into domo. (Ex. "Customer called in to discuss work order W571191476628566, and asked about order status").
In the domo ETL I can use regex to locate the work order number, but the tiles only allow me to replace the regex or use it as a delimiter. Is there a way to extract the regex and keep it in a populate it in a column/field?
Answers
-
Hi @rwalsh25
If you're wanting to keep the original text and split work order number into a new column you'd need to split your dataset, pull just the key fields and the note field, then use the regex and then join the data back together using your key fields. If you're using ETL 1.0
If you're using ETL 2.0 you can just use a formula tile to define your new column name and run the regex_replace function on your notes field.
REGEXP_REPLACE(`note`, '^.*(W\d{15}).*$', '$1')
^ = start of the string
.* = 0 or more of any characters
(W\d{15}) - Captures the work order number in the format of W followed by 15 digits.
$ = end of the string
Simply match the entire string and replace it with only the work order number
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3 -
Thank you @GrantSmith ! It looks like the formula tile is able to extract the work order on occasions, but I am still noticing a few situations where the formula is not extracting just the work order:
Using the Formula tile REGEXP_REPLACE(`Note content`, '^.*(W\d{15}).*$', '$1') ) to create Work Order Number column:
Example 1: Does not extract WO# only
Note Content:
R261485821 [url link to page being referenced that contains work order number] W144650992267872 [url link to page being referenced that contains work order number]
Work Order Number:
R261485821 [url link to page being referenced that contains work order number] W144650992267872 [url link to page being referenced that contains work order number]
Example 2: Does extract WO# only
Note Content:
Replacement for W820711785371520 [Url link that contains work order Number]
Work Order Number:
W820711785371520
My goal is to have a new column that contains only the work order number so that I could join this dataset to other datasets.
Thanks again!
0 -
Even a stranger occurrence that I found:
Note Content:
Row 344: W662956731987580 [https:/work_orders##queue.filter.work_order_number_starts_with=W662956731987580;queue]
Row 345: W907563388633513 [https:/work_orders##queue.filter.work_order_number_starts_with=W907563388633513;queue]
Work Order Number:
Row 344: W662956731987580 [https:/work_orders##queue.filter.work_order_number_starts_with=W662956731987580;queue]
Row 345: W907563388633513
Same exact character and format length in the notes content, yet for some reason the work order number was only extracted for one, while the other kept the entire note content.
0 -
You might have some line breaks in your string causing issues. We can try and pass in the 'm' modifier flag to treat it as a multiline string:
REGEXP_REPLACE(`note`, '^.*(W\d{15}).*$', '$1', 'm')
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive