Data Preparation

[poojamate92]

 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

[Philip · Accepted solution]

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 solution]

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 solution]

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