Alteryx Designer Desktop Discussions

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

extract substring after certain word(s)

Drew502
5 - Atom

I have a long string where I am looking to extract two values: 

1: The numeric value that appears after "fullAcctNumber"

2. The string (can be multiple words) that appears after "Instrument, paramVal=". 

 

As in the attached example, there can be multiple instances within the same string. I need to extract all instances within the string

ideal output (can be in multiple columns or rows):

"123456789:Individual"

"987654321:IRA"

 

 

string example:

acctType=INV, extAcctType=INV, acctNumber=123456789), AcctName(paramName=NickName, paramVal=Bobs Brokerage)], acctOwnerName=null, fullAcctNumber=123456789), acctDateTime=[AcctDateTime(dateTimeType=LastUpdateAttempt, dateTimeVal=2023-09-09), property=[Property(paramName=RetirementStatus, paramVal=N R), Property(paramName=Instrument, paramVal=Individual), Property(paramName=AccountOwnership, paramVal=Individual)]), acctType=INV, extAcctType=INV, acctNumber=987654321), AcctName(paramName=NickName, paramVal=Bobs IRA), acctOwnerName=null, fullAcctNumber=987654321), acctDateTime=[AcctDateTime(dateTimeType=LastUpdateAttempt, dateTimeVal=2023-09-09), property=[Property(paramName=RetirementStatus, paramVal=R), Property(paramName=Instrument, paramVal=IRA), Property(paramName=AccountOwnership, paramVal=Individual)])])]))

5 REPLIES 5
FinnCharlton
13 - Pulsar

Hi @Drew502 , you can do this with the Tokenize option of the Regex tool:

 

image.png

Drew502
5 - Atom

@FinnCharlton This is great!! Huge help!! Thank you.

For the below section of the expression, how could we modify it to look for multiple words? It looks like it is stopping when there is a space. The values commonly have spaces in them (ex: Rollover IRA, Roth IRA, Brokerage, Joint, Trust, etc). If it helps, there will always be a closed parenthesis at the end of the string.

 

Instrument, paramVal=\w+

 

FinnCharlton
13 - Pulsar

@Drew502 very good point, you can tell it to take everything it finds before the bracket, like this:

 

Instrument, paramVal=[^\)]+
Drew502
5 - Atom

@FinnCharlton Thanks again for your help on this! Unfortunately my data has changed and the reg ex broke. I've tried playing around with it but I have a feeling the quotes are what is throwing me off.

 

As a reminder, I have a giant string that I am trying to return 2 values

1: The numeric value that appears after "FullAcctNumber\" : \"  (note: the number of astrix can vary)

2. The string (can be multiple words) that appears after Instrument\",\n \"ParamVal\" : \"


There can be multiple instances of both values

 

Example:
"FullAcctNumber\" : \"*****1234"  .................................    Instrument\",\n \"ParamVal\" : \"Roth IRA\"

FinnCharlton
13 - Pulsar

Hey @Drew502, try this Regex with the Tokenize option:

 

"FullAcctNumber\\" : \\"\d+?"|Instrument\\",\\n \\"ParamVal\\" : \\".+?\\"

 

 

 

 

Labels
Top Solution Authors