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

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.

Datasets

Dynamic Processing

Accepted answers

Qiu

@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.

0329-WCH.yxzp

All comments

Qiu

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

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

0329-WCH.yxzp

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.

2021-03-29_11-25-26.png

2021-03-29_11-25-38.png

2021-03-29_11-24-44.png

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703