Aggregating values based on row string

[SRawcliffe]

Hi alteryx community, 

I'm a relatively new user. I've attached some dummy data to outline what I am trying to do. I would like to combine countries, england, n.ireland, scotland and wales into "UK" for each group. So there will be a row that says "UK" and the corresponding values under cats, dogs etc are summed from the individual countries. EU and other overseas would remain as is.

What is the best way to go about this? My initial thought was use a formula but there are a lot of functions in alteryx that I am still learning so any help is appreciated! 

Thanks! 

[DavidSkaife · Accepted solution]

Hi @SRawcliffe 

This should work for you:

Filter out where the Country is England, N.Ireland, Scotland or Wales, then using the Summerise tool group by Group and sum the fields required. Next add in a new Country called UK for this stream, and union the data back to the False output of the Filter tool. Last two tools are just tidying up the positions of the fields and sorting by Group

Workflow attached

[SRawcliffe]

Thats perfect, thanks you!