topic Re: grouping based on part field name in Alteryx Designer Desktop Discussions

grouping based on part field name

alex_reevoo — Thu, 24 May 2018 01:45:22 GMT

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

Re: grouping based on part field name

jdunkerley79 — Tue, 07 Jun 2016 17:50:55 GMT

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

Re: grouping based on part field name

alex_reevoo — Wed, 08 Jun 2016 08:49:35 GMT

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

Re: grouping based on part field name

jdunkerley79 — Wed, 08 Jun 2016 08:54:53 GMT

Attached as a 10,0 file

Re: grouping based on part field name

alex_reevoo — Wed, 08 Jun 2016 08:55:57 GMT

Legend, thank you!!

Re: grouping based on part field name

alex_reevoo — Wed, 08 Jun 2016 10:02:38 GMT

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

Re: grouping based on part field name

jdunkerley79 — Wed, 08 Jun 2016 10:17:46 GMT

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

Re: grouping based on part field name

alex_reevoo — Wed, 08 Jun 2016 13:57:09 GMT

Thanks James, looking forward.

Alex

Re: grouping based on part field name

jdunkerley79 — Wed, 08 Jun 2016 16:41:43 GMT

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.