Row duplication and text editing

[tracyjclark]

Hi!

I am pretty new to Alteryx and am trying to create a payroll reconciliation.

I have figured out what to do with my data from the GL, but the pay register data is a bit different. The pay register data has a column for ledger account number. When more than one ledger account is hit, they are shown in the same cell. (i.e: 

10000:Payroll Withholdings Clearing60000:Benefits)

First of all, I need to duplicate the rows where these occurrences happen and I need to make sure that the ledger account column has one of the rows to just have 10000:Payroll Withholdings Clearing in the ledger account and the other to just have 60000:Benefits.

For visual aid, this is what I'm starting with:

Amount	Ledger Account
100	10000:Payroll Withholdings Clearing6000:Benefits

And this is what I want:

Amount	Ledger Account
100	10000:Payroll Withholdings Clearing
100	60000:Benefits

Please let me know if you have any solutions!

Thanks!

[fmvizcaino · Accepted solution]

Hi @tracyjclark ,

I have created an example using regex and identifying patterns to achieve what you need.

Take a look at the attached example and let me know if that works for you.

Best,

Fernando Vizcaino

[tracyjclark]

Yes this worked perfectly, thanks so much! I just copied and pasted the expression you used, but for my knowledge, what does it mean/what exactly is it doing? @fmvizcaino

[fmvizcaino]

Hi @tracyjclark ,

The 'tokenize' option means that for every pattern inserted in regular expression text box identified in your text, it will create automatically a new column/row

The pattern: \d+:[^\d]+

10000:Payroll Withholdings Clearing60000:Benefits

\d      any digit

\d+    any digit one or more times

:        : character

[]       group of possibilities

^\d    anything but a character

[^\d]+ anything but a character one or more times

To understand a lot more about regex, take a look at this topic.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

Best,

Fernando Vizcaino