Data Preparation

Question

Hello,

I have a input file in which contain unstructured data. In file there is number directly following to character 'XYZ'. These are dollar amounts for transactions directly tied to an individual. There can be multiple transactions per row. I have to calculate row wise sum of these number

Intermediate_Step2_RegExAnalytics.yxmd

Philip · Accepted Answer

Here's a solution. First, add a record ID for keeping track of rows. Next, use RegEx to parse each number into separate rows. Finally, sum by row.

NicoleJohnson · Accepted Answer

Try the following (sample workflow attached).

1. Add a RecordID to your data

2. Use RegEx Tool to Tokenize (split to rows) the XYZ. followed by the numbers: (XYZ[.]\d+[.]*\d*)

3. Use another RegEx Tool to Replace the XYZ. and leave only the numbers: XYZ.(.*), replace only what is marked field $1

4. Select Tool to change field to double

5. Summarize Tool to Group by RecordID and Summarize the Field with the transaction amounts

Does that provide what you need? :)

NJ

NicoleJohnson · Accepted Answer

... or use @Philip solution because it's pretty much identical to mine except that it uses one fewer tool... :)