Regex formula works in ETL preview but doesn't show up in dataset output?
I'm using the Dataset from Email connector to push a weekly email into Domo. The body of the email contains an account balance, which I'm trying to extract into a new field. The body of the email will always contain "Amount: USD 123,456.00"
I created a regex to match what comes after the "USD " and before the next whitespace, and in the preview for the formula tile it shows the value I'm expecting. However, when I run the dataflow, the new field is blank.
case when REGEXP_LIKE(`body`,'^.*(?<=USD ).*?([^\s]+).*$') then REGEXP_REPLACE(`body`,'^.*(?<=USD ).*?([^\s]+).*$','$1') end
Answers
-
The first part of that regular expression may not work with Domo SQL because part of that is a "lookbehind", which can be finicky. If you try to isolate the numeric part following the USD, you could try something like:
CASE
WHEN REGEXP_LIKE(`body`, 'USD\s+\d{1,3}(,\d{3})*(\.\d{2})?')
THEN REGEXP_REPLACE(`body`, '.*USD\s+(\d{1,3}(,\d{3})*(\.\d{2})?).*', '$1')
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Also, another thing to consider is whether any downstream operations are being performed on the Amount Paid field. In other words, maybe the regex is working but there is another step in the workflow that is causing it to become a NULL value.
0 -
I tried this and am getting the same thing. The preview renders, but the output is empty. I'm not doing any other operations after this in the dataflow.
CASE
WHEN REGEXP_LIKE(`body`, 'USD\s+\d{1,3}(,\d{3})*(\.\d{2})?')THEN REGEXP_REPLACE(`body`, '.*USD\s+(\d{1,3}(,\d{3})*(\.\d{2})?).*', '$1') end
0 -
I'd suggest adding an else so that you can determine whether the issue is that your WHEN isn't evaluating to true or whether the issue is with the replace
0 -
If the preview works but the output is empty, it might be there are characters unseen. Have you tried using trim/replace? I'm not sure if this formula is quite right as I'm not in an example.
CASE
WHEN REGEXP_LIKE(REGEXP_REPLACE(`body`, '^\s+|\s+$', ''), 'USD\s+\d{1,3}(,\d{3})*(\.\d{2})?')
THEN REGEXP_REPLACE(REGEXP_REPLACE(`body`, '^\s+|\s+$', ''), '.*USD\s+(\d{1,3}(,\d{3})*(\.\d{2})?).*', '$1')
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
It's getting closer. Now, it starts with the dollar amount but also grabs everything that comes after that.
case
when REGEXP_LIKE(trim(`body`),'^.*USD\s+(\d{1,3},\d{1,3}\.\d{1,2}).*')
then REGEXP_REPLACE(trim(`body`),'^.*USD\s+(\d{1,3},\d{1,3}\.\d{1,2}).*','$1')
else 0 end0 -
I am not a fan of regex as I find it difficult to read, maintain and troubleshoot. I would suggest the following:
Take this example:
I use the formula tile and start by using the RIGHT() and INSTR() to find the starting position of Amount USD:
I then use the SPLIT_PART() function to look for the amount by getting the string after the third space and stops when it gets to the fourth space. This eliminates all the text after the amount.
Finally, I format it as a decimal by replacing the comma with nothing and then using TRY_CAST() function to safely convert it.
Hope this helps.
**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 -
If you continue with regexp, you can try something like this…
CASE
WHEN REGEXP_LIKE(REGEXP_REPLACE(`body`, '^\s+|\s+$', ''), 'USD\s+\d{1,3}(,\d{3})*(\.\d{2})?')
THEN REGEXP_REPLACE(REGEXP_REPLACE(`body`, '^\s+|\s+$', ''), '.*USD\s+(\d{1,3}(?:,\d{3})*(?:\.\d{2})?).*?', '$1')
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
I agree with Mark, for something like this you're better off without regex. I would note, however, that you're also slightly better off just using two SPLIT_PART invocations, like this:
SPLIT_PART(SPLIT_PART(my_string, 'My Prefix', 2), 'My Suffix', 1)
Paired SPLIT_PART invocations like this will give you the part of your string that appears between the two constant strings. So, for our specific case:SPLIT_PART(SPLIT_PART(body, 'Amount: USD ', 2), ' ', 1)
I'd also recommend using SQUASH_WHITESPACE on the body first thing, so that subtle differences in the text don't cause the process to fail, like multiple spaces appearing in between 'Amount:' and 'USD' or 'USD' and the digits, or perhaps a newline appearing after the digits instead of a space. SQUASH_WHITESPACE will take care of all those cases:
SPLIT_PART(SPLIT_PART(SQUASH_WHITESPACE(body), 'Amount: USD ', 2), ' ', 1)
In Mark's example, this would replace the "amount" formula and the "StringWithAmount" would be unnecessary. Though you could of course split this formula into another named stage if you desired.
All of that said, I am still curious about what was happening that was causing the preview to produce a result while the execution was not. Unfortunately the screenshot does not include enough of the result row to determine whether the failure was happening for the same input value as had been working in the preview. There would be no difference in the regex behavior between the two, so there must have been a difference in the input data the two were working with. Could it be that the Input DataSet tile is set to load only new rows? Just a curiosity.Randall Oveson <randall.oveson@domo.com>
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive