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

Parse fields based on multiple keywords into multiple new fields

NeilFisk
9 - Comet

I have been attempting to parse a long string of text that a variety of keywords that may or may not be in the same order from row to row.  I first attempted to replace each keyword with a pipe and then use the text-to-columns tool to break them out into separate fields.  The issue with this is that I don't necessarily know which keyword the parse refers to.  I attempted also to throw in an equation to count the number of characters before the keyword to know which one it was (i.e., to know the order) but that got very messy.

 

I'm not great at REGEX but I believe this can be done easily using the RegEx tool.  Is there some way to do this with RegEx so that, knowing the keywords I want to parse on, that fields are created for each new keyword with the text that corresponds with it?

 

I have attached an example input and Desired Output (as two text inputs in a blank workflow).

 

Thanks!

10 REPLIES 10
apathetichell
18 - Pollux

Interesting - are the keywords always set or are they fluid? is there always a clear demarcation of the information you want after the keyword - ie. does it always end in .?

MichaelSu
Alteryx Alumni (Retired)

@NeilFisk ,

 

Please see attached workflow and let me know your thoughts. Rather than using RegEx, you can try to use the Split to Rows.

 

MichaelSu_0-1626297551997.png

 

 

Thanks,

Mike

NeilFisk
9 - Comet

Mike,

 

I can't use the : as the delimiter as it may be used in the sentence and not contained directly after the keyword.  However, I was able to use RegEx with a simple find/replace to find something like ISSUE: and replace it with ISSUE|, etc.  This allows me to use the Text To Columns tool as you suggested.  The important piece I was missing was the use of the multi-row formula.  After doing this, I was able to get the results I needed.

 

Thanks,

Neil

NeilFisk
9 - Comet

Keywords are always the same and generally have a : afterwards, however, : may be present elsewhere in the text, so although Mike suggested to use that as the delimiter to use text to columns, I had to change that first.  I can't rely on the paragraph always ending in a period either, as they may have missed one, or there may be multiple sentences to parse out.  Mike had the right direction with a multi-row formula. 

 

I still think that some form of REGEX would still work, but I'm no expert in that.

 

Thanks!

MichaelSu
Alteryx Alumni (Retired)

Glad that you were able to get your results with the multi-row formula!

apathetichell
18 - Pollux

glad to hear it worked out @NeilFisk  - as an fyi - not sure if you adjusted it on your regex attempts but | is part of regex syntax so to use it as just a character you'lll have to use \| - this could be part of the problem why your regex wasn't working...

NeilFisk
9 - Comet

That's not an issue as I was just using a simple replace option for REGEX and the pipe was added not in the expression but as the replacement text.  You can see the screenshot below.  Although this works, I was hoping someone would also show a solution truly using REGEX.

 

Capture.PNG

apathetichell
18 - Pollux

Something like this? Let me know if this is heading in the right direction - id doesn't have the non-matched as a separate category, but this is how I would approach it via regex.

NeilFisk
9 - Comet

Rather complex with the macro.  I was thinking that the RegEx tool under the Parse tab could be written in such a way to contain the entire logic.

Labels