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

demo.csv

jdunkerley79

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

Create pct totals by side.yxzp