grouping based on part field name

[alex_reevoo]

Hi All

I need to run some analysis on a file similar to the attached.

As you can see, products have scores which are split into positive and negative, which means each product has two columns each. row cell contains a number which represents the weight of that positive or negative score

What I'd like to do is transform the data so that so that there is only one column per product, and an overall % of positive or negative

                 Product 1 | Product 2

Negative         35%        52%

Positive           65%       48%

What are your thoughts ? is this achievable ? 

Alex

[jdunkerley79 · Accepted solution]

Hi,

Yep prefectly possible,

First add a record ID and then transpose t get the field name as data.

Then I chose to use a regex tool to break product Id and score type into columns

Next a couple of summarisation to get total and total by score type.

FInally a cross tab to put back together.

It's dinner time for me so apologies that attached flow is a bit rougher than I would like

[alex_reevoo]

Hey thanks for your reply.

Quick one, are you able to send me the file as .yxmd? I'm having to use an older version of Alteryx and cant open the yxzp file :(

Alex

[jdunkerley79]

Attached as a 10,0 file

[alex_reevoo]

Legend, thank you!!

[alex_reevoo]

Hey there,

Quick question on top of this. Let's say that each product has different names, iPhone, xbox, etc.

How would the parse tool work in this scenario?

Thanks again for continued assistance.

Alex

[jdunkerley79]

Hi Alex,
would just be a change to the Regex. I am not in front of Alteryx at the moment so this is from memory. 
I think the expression would be ^(.*)\(([^\(]*)\)$
Will stick a demo together later. 
James

[alex_reevoo]

Thanks James, looking forward.

Alex

[jdunkerley79]

Ended up using:

^\s*(.+?)\s*\(\s*([^\)]+?)\s*\)s*$

As this expression will cope with leading an trailing spaces all over the header.

Attached as 10.0 again.