RegEx Split Assistance

Question

Hello,

I have the following table. other columns hidden. Kicks this way out of the CRM (don't have a direction connection to the database). Can't fix the CRM output. We have been manipulating it before we load into sql on our server where we do all the reporting an analysis. Would rather do in Alteryx.

Account NumberModel123M&M - 100%438M&M - 60%  |  TJ - 05%  |  AS - 10%  |  TD - 025%753M&M - 60%  |  TJ - 00%  |  AS - 40%  |  TD - 000%1068M&M - 60%  |  TJ - 00%  |  AS - 40%  |  TD - 000%

I'm looking for the best tool / way to get the data in the following format.

Account NumberModelM&MTJASTD123M&M - 100%100%0%0%0%438M&M - 60%  |  TJ - 05%  |  AS - 10%  |  TD - 025%60%5%10%25%753M&M - 60%  |  TJ - 00%  |  AS - 40%  |  TD - 000%60%0%40%0%1068M&M - 60%  |  TJ - 00%  |  AS - 40%  |  TD - 000%60%0%40%0%

AngelosPachis · Accepted Answer

Hi @michael_franz ,

The most dynamic way is to use the " | " as a delimiter to split on rows, and then use the "-" to split headers from actual values. Then it's just a matter of cleaning your data and joining back in the original dataset.

Hope that helps,

Angelos

MarqueeCrew · Accepted Answer

@michael_franz,

I was hoping that your request for "Best" would get more responses (e.g.  @PhilipMannering ).  I'm going to give you a non-RegEx solution that solves the challlenge in 1 tool.  I use GetWord() and IF statements to achieve a result that is faster than the provided solution.

It is "best" for time, but does expect a very static input layout.

Cheers,

Mark