Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Need help to extract variable length string from a formatted column

MBR
5 - Atom

Hi there,

 

I have a column with data comprised mostly of formatted data "variable1":"value1" or "variable2":value2

 

"action":"eCreateCase","status":null,"message":{"chunkSize":1,"listofArticles":[{"usefulCounterPercent":0,"usefulCounter":0,"notUsefulCounter":0,"articleTitle":"This Is The String - I Want To Extract","guid":null,"viewCounter":0,"url":"https://www.google.com" …

 

I want to extract the value of articleTitle and put it in a new column.  It is a variable length string that will always be in between articleTitle and guid.  I am not great with regex but can match the string with 

 

(?<=articleTitle":").*(?=","guid)

 

I can't figure out how  to use this so that I  end up with a new column where the result is:

 

This Is The String - I Want To Extract

 

Thanks in advance!

4 REPLIES 4
apathetichell
18 - Pollux

2021-05-06.png

To Regex or Not To Regex - that is the question????

 

Try this one - There's probably an easy regex way (like throw the (.*) in a parenthesis and use $1 in regex tool/replace mode non-matching text. But I like this one.

MBR
5 - Atom

Wow, thanks for this quick reply apathetichell!  The Text to Columns is throwing me off though.  I should have explained my data structure better.  I am trying to create the Blue column and fill it in for each row.

 

Session #NameOutputArticleTitle
123Bob

"action":"eCreateCase","status":null,"message":{"chunkSize":1,"listofArticles":[{"usefulCounterPercent":0,"usefulCounter":0,"notUsefulCounter":0,"articleTitle":"This I Is The String - I Want To Extract","guid":null,"viewCounter":0,"url":"https://www.google.com"

This I Is The String - I Want To Extract
456Fred

"action":"eCreateCase","status":null,"message":{"chunkSize":2,"listofArticles":[{"usefulCounterPercent":0,"usefulCounter":0,"notUsefulCounter":0,"articleTitle":"I Want This String Too","guid":null,"viewCounter":0,"url":"https://www.bing.com" …

I Want This String Too

 

apathetichell
18 - Pollux

Hi,

 

My TTOC should work - if not---- check out my screen grab on the other post... ie the two steps you were missing to complete your regex version.

 

Note - both version (regex/non-regex) are working on my system with those two entries at least. Obviously with a TTC (set tor  Rows) solution it is imperative that you keep your row number throughout to match the row with the text you want.

MBR
5 - Atom

Aha!  It was the checkbox "Copy unmatched text to output" in the Regex that was messing me up.  Thanks so much for attaching that screenshot!  I do think both solutions are working but the Regex seems like less bookkeeping to me.  Much appreciated!!

Labels