Alteryx Designer Desktop Discussions

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

REGEX to parse a words and positive/negative amounts

Faye_Karas
7 - Meteor

I have data that looks like below.  I'm trying to use regex to parse the amounts (both positive and negative) into separate columns and the before and after verbiage into their own columns.  I used regex as shown below but got error message:

Error: RegEx (151): There was an error in regex_search The complexity of matching the regular expression exceeded predefined bounds. Try refactoring the regular expression to make each choice made by the state machine unambiguous. This exception is thrown to prevent "eternal" matches that take an indefinite period time to locate.

 

Please assist.

 

Regex:

\s+?\s+?([^\s].*?)\s+?([^\s].*?)\s+?([^\s].*?)\s+?([^\s].*?)\s+?([^\s].*?)\s+?([^\s].*?)\s+?

 

Sample Data:

CURR MONTH BILLED 1331.81 0.00 18008.64 0.00 0.00 19340.45 ABC COMPANY
CURR MONTH BILLED 0.00 0.00 532.80 0.00 0.00 532.80 XYZ COMPANY
YTD ALLOC COLLECTN 568780.71 0.00 545507.39 0.00 0.00 1114288.10
BEG YR/TRANS IN BAL 0.00 0.00 -26.35 0.00 0.00 -26.35 ACCOUNT: 111428
CURR MONTH BILLED 0.00 0.00 0.00 0.00 0.00 0.00 GENERAL COMPANY
BEG YR/TRANS IN BAL 0.00 0.00 21.87 0.00 0.00 21.87 ACCOUNT: L12345
CURR MONTH BILLED 0.00 0.00 1715.29 0.00 0.00 1715.29 ABC & ASSOC

2 REPLIES 2
Qiu
21 - Polaris
21 - Polaris

@Faye_Karas 
Based on the error message, it says your Regex is too complex to handle.

Anyway, for the fixed number of columns and a dynamic approach for changing of number of columns, maybe something as below.

0122-Faye_Karas-A.PNG0122-Faye_Karas-B.PNG

Faye_Karas
7 - Meteor

Thank you!  Based on my preliminary attempt, the Dynamic option seems to work!  I will keep looking at this but in the meantime THANK YOU!!!

Labels