How to sum the quantity of multiple fields that match certain categories

[WCH]

Hi Guys,

I have been stuck at this workflow, and would appreciate if you could help 🙂

The dataset is pretty much like below template, category of each type of pets and their respective quantity are presented in 2 lines of each stores. I need to provide the sum/Total quantity of these 4 different pets: cat, dog, bird and hamster.

Each pet type doesn't comes in with the same sequence in each store. 

StoreA	category	cat	dog	fish	bird	snake	lizard	hamster
StoreA	quantity	5	6	8	6	3	2	9
StoreB	category	dog	snake	snail	hamster	cat	rats	bird
StoreB	quantity	2	3	4	6	1	1	7
StoreC	category	bird	rabbits	cat	lizard	hamster	dog	shrimp
StoreC	quantity	2	6	7	1	8	3	2

Thank you all in advance.

[Qiu · Accepted solution]

@WCH 
yesteryday, I answered a similar question to yours.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Transpose/td-p/740072
I would like to try with the same approach.
One thing, I am sure that  what will be the Header of your data, and I assumed its something general.

For the summarize part, I did it per store, and you may need to adjust a bit for your requirement.

[WCH]

@Qiu Thank you for replying so quick.

I am trying to understand and run the workflow that you provided, but I encounter an error:

Failure to Import C:\Users\abc\Downloads\0329-WCH.yxzp.
There was an error opening "C:\Users\abc\Downloads\0329-WCH\0329-WCH.yxmd":

This workflow was created by a more recent version of Alteryx, and may contain tools or functionality not present in this version. Alteryx does not support using an earlier version of Alteryx to open a workflow created with a newer version. For best results, download the latest version of Alteryx.

Do you still want to attempt to open the workflow?

Not very sure if it is really because of my Alteryx version is too old. Anyway, I tried to import the workflow and the macro separately, and the error I got on the macro is :" The Control Parameter "Control Parameter (6)" must be mapped to a field." Then I stuck again, don't know where and how to fix this error. Can you please help?

Thanks again, I am not very good at macro, and at first I was trying to solve the query without macro, now seems it is inevitable. 

[WCH]

@Qiu I just wants to update you, I just slightly changed the workflow you shared with me and it finally works. 😆

I am not that great at building macros but your sharing really inspire me to take a deeper look into it. Thanks again!

[Qiu]

@WCH 
Thank you for the feedback. and glad to know it works.
I do agree usually the it requires some small or big modifications when it is being used in the realy business  flow.

If you dont mind, I would appreciate if you could mark my answer as aceept so it can help others as well. 😁