Regex formula works in ETL preview but doesn't show up in dataset output?

Options
ccccc4
ccccc4 Member
in Magic ETL

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.

Screenshot 2024-10-25 113031.png

case when REGEXP_LIKE(`body`,'^.*(?<=USD ).*?([^\s]+).*$') then REGEXP_REPLACE(`body`,'^.*(?<=USD ).*?([^\s]+).*$','$1') end

Screenshot 2024-10-25 113009.png

Answers

  • ArborRose
    ArborRose Coach

    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! **

  • ggenovese
    ggenovese Coach

    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.

  • ccccc4
    ccccc4 Member
    edited October 2024

    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
  • ggenovese
    ggenovese Coach

    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

  • ArborRose
    ArborRose Coach

    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! **

  • ccccc4
    ccccc4 Member

    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 end
    Screenshot 2024-10-25 141209.png
  • MarkSnodgrass
    MarkSnodgrass Coach

    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:

    image.png image.png

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ArborRose
    ArborRose Coach

    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! **

  • rco
    rco Contributor
    edited October 2024
    https://community-forums.domo.com/main/discussion/comment/99091#Comment_99091

    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>

Categories