Rearrange data

[SylviaK]

Hi, 

Can someone please advise how to get from the below Input table to the below Output table?

Many thanks for your help in advance.

[Thableaus · Accepted solution]

Hi @SylviaK 

Here's a way of doing. Workflow attached.

Split your category column into rows using "," as a delimiter. Use the Data Cleansing to remove extra whitespaces. Use the Summarize tool to Group by Category and do the count.

Cheers,

[JReid · Accepted solution]

This was a nice quick challenge. Parse tools are one of the biggest hurdles, but most useful tools in Alteryx.

I think the attached file has the solution you're looking for!

EDIT: ahh @Thableaus beat me to it. Same solution too!

[SylviaK]

Thank you Thableaus. It works perfectly. 

However, just realised that my data is in this format, so separating by comma doesn't work. Any ideas?

[Thableaus · Accepted solution]

@SylviaK 

Try using this option in the Text to Columns tool.

It will ignore any delimiters found inside quotes.

Cheers,

[SylviaK]

Brilliant. That works. 

after the data into rows, would like to get rid of the quotes around my category. how do I do that?

[Thableaus · Accepted solution]

@SylviaK 

Use the Replace expression. Add a formula tool. Add this expression:

Replace ([Category], '"', "")

You would be replacing quotes for nothing.

Cheers,

[SylviaK]

many thanks @Thableaus