Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Regex Replace used in formula tool

yvkpatel
6 - Meteoroid

I am trying to replace below strings using Regex:

1) interest payable pursuant to this Section 3(d)(ii) on an Installment Due Date will be $108,452.04.

2) "Fixed Interest Rate" means the annual interest rate of 4.36%.

 

Desired result: $108,452.04      #for 1st line

                        4.36%                #for 2nd line

 

I am using the following methods for the respective lines:

if REGEX_Match([Field],".*fixed interest rate.*") then REGEX_Replace([Field],"(.*?)(\d+.*%)","$1.$2%")

 

if REGEX_Match([Field], ".*interest payable pursuant.*") then REGEX_Replace([Field], "(.*?)(\$\d+.*?)(,\d+.*)", "$1")

 

The result that I get using the above codes are the same previous lines, there is no change.

 

I don't know what I am doing wrong. Can anyone please correct my code?

13 REPLIES 13
estherb47
15 - Aurora
15 - Aurora
Hi,

You’re so close!! Within then regex replace, the what to replace with part of the formula should $2 not $1. Each parenthetical is given a number, from 1 to n. You don’t need to include all of them in the result.

First formul should return $2, and second should return $3.

Let me know if that helps!

Cheers,
Esther
Thableaus
17 - Castor
17 - Castor

Hi @yvkpatel 

 

Would this work?

 

IF Contains([Field1], "Fixed Interest Rate")
THEN
REGEX_Replace([Field1], ".*?(\d+\.?\d+%).*", "$1")
ELSEIF Contains([Field1], "interest payable pursuant") THEN
REGEX_Replace([Field1], ".*?(\$.*\b).*", "$1")
ELSE [Field1] ENDIF

 

Cheers,

yvkpatel
6 - Meteoroid

Thank you very much for helping me out learn and providing me a solution.

 

I got the correct output now.

estherb47
15 - Aurora
15 - Aurora
Wonderful!! Glad it’s working for you now.

--
Esther Bezborodko
*Senior Manager*
201.650.7314 | estherbezborodko@gmail.com
beautycounter.com/estherbezborodko

*Our mission is to get safe products in the hands of everyone.*
[image: Facebook]
yvkpatel
6 - Meteoroid

Hello there,

Now I am trying the same thing with the statement:

 

an actual/360 interest calculation schedule (interest is payable for the actual number of days in each month, and each month's interest is calculated by multiplying the unpaid principal amount of this Note as of the first day of the month. Each monthly payment of principal and interest will first be applied

 

with the code:

if REGEX_Match([Field],".*interest calculation schedule.*") then REGEX_Replace([Field],"(.*?)(\w+\/\w+)(.*)","$2")

 

I also tried the code:

if REGEX_Match([Field],".*interest calculation schedule.*") then REGEX_Replace([Field],".*?(\w+\/\w+).*","$1")

 

to get the output as:

actual/360

 

Still not getting the proper output from either of the codes.

 

Can anyone please help me out here if I am still missing out something which I haven't encountered before?

SamDesk
11 - Bolide

Hi @yvkpatel 

 

Just wanted to check that your code does include the rest of the IF statement and not just what you shared, otherwise you'll be getting syntax errors. So it should in full look something like the below:

IF
REGEX_Match([Field],".*interest calculation schedule.*") 
THEN
REGEX_Replace([Field],".*?(\w+\/\w+).*","$1")
ELSE
Null()
ENDIF

 

I have tried to run this with your sample statement and get the desired output of "actual/360".

 

Sam 🙂

yvkpatel
6 - Meteoroid
Hi,
I have used this along with other 'IF' statements using 'ELSEIF'.

So is there any problem with using more elseif statements? The above statement was in the 3rd elseif condition.
SamDesk
11 - Bolide

Hi @yvkpatel 

 

What is probably happening then is that one of your previous conditions is returning true and so it never gets to your later elseif statement. You may need to tighten your previous conditions so they don't return true where you don't want them to.

 

You could split your current formula into separate fields and then you can see which conditions trigger to diagnose which statement is problematic.

 

Sam 🙂

SamDesk
11 - Bolide

Hi @yvkpatel,

 

I've just tested it with the previous lines from earlier in this discussion and I am still getting your desired output.

 

Full IF statement:

 

IF 
REGEX_Match([Field],".*fixed interest rate.*") 
THEN 
REGEX_Replace([Field],"(.*?)(\d+.*%)","$2")

ELSEIF
REGEX_Match([Field], ".*interest payable pursuant.*")
THEN
REGEX_Replace([Field], "(.*?)(\$\d+.*?)(,\d+.*)", "$2$3")

ELSEIF
REGEX_Match([Field],".*interest calculation schedule.*") 
THEN
REGEX_Replace([Field],".*?(\w+\/\w+).*","$1")

ELSE
Null()
ENDIF

 

 

Can you share your workflow to aid in diagnosing your problem further?

 

Sam 🙂

Labels