RegEx Split Assistance

[michael_franz]

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 Number	Model
123	M&M - 100%
438	M&M - 60%  |  TJ - 05%  |  AS - 10%  |  TD - 025%
753	M&M - 60%  |  TJ - 00%  |  AS - 40%  |  TD - 000%
1068	M&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 Number	Model	M&M	TJ	AS	TD
123	M&M - 100%	100%	0%	0%	0%
438	M&M - 60%  |  TJ - 05%  |  AS - 10%  |  TD - 025%	60%	5%	10%	25%
753	M&M - 60%  |  TJ - 00%  |  AS - 40%  |  TD - 000%	60%	0%	40%	0%
1068	M&M - 60%  |  TJ - 00%  |  AS - 40%  |  TD - 000%	60%	0%	40%	0%

[AngelosPachis · Accepted solution]

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

@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 

[michael_franz]

I believe I understand your solution, but it does not contemplate the growth in employees. TJ, AS, TD, etc are all employees. We we hire or reassign, I'll have to continue to make changes to the formula. The other way handles those changes.

[MarqueeCrew]

I did not interpret those requirements.  But did worry about such a static solution. 

cheers,

 mark