Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Sum If in Alteryx

mrich93
7 - Meteor

Hi all,

 

I know there are some answers to this predicament on here but want some more clarity. 

 

I have a file that is 1 GB full of sales transactions, however I just need to sum up all the repeating UPCs into one sum total. Sample size of an example of how the data is displayed.

 

UPCDESCRIPTIONFAC CODEREGIONENTITYQTY SHIPPED
UPC1UPCDEC1ATLSOENT11
UPC1UPCDEC1ATLSOENT12
UPC2UPCDEC2SARFLENT23
UPC3UPCDEC3ATLSOENT24
UPC2UPCDEC2AURRMENT15

 

I need in theory to output, UPC1>UPCDEC1>ATL>SO>ENT1>1+2=3

 

So on and so forth. But I also need to output the same thing but the total of ENT2 separately so I DONT want that in the total for ENT1. Preferably that to output in another column on the same total row. 

 

I'd like it look like the below:

UPCDESCRIPTIONFAC CODEREGIONENTITY1QTY SHIPPED SUM TOTAL 1ENTITY2QTY SHIPPED SUM TOTAL 2
UPC1UPCDEC1ATLSOENT13ENT20
UPC2UPCDEC2SARFLENT10ENT23
UPC3UPCDEC3ATLSOENT10ENT24
UPC2UPCDEC2AURRMENT15ENT20

 

Any help would be appreciated thanks! 

5 REPLIES 5
JulioMO
9 - Comet

Hi @mrich93 

 

I've Attached a workflow that gets the job done.

 

Let me know if it works for you.

 

Regards!

mrich93
7 - Meteor

Would I need a dynamic input after the text input before select tool to input my data in? 

JulioMO
9 - Comet

That depends.

 

If you transactional data is located in just one file (i.e. .xlsx; .csv, other), you will just need an input tool. In my example, I've used a text input since I don't have an external file to input, but you do.

 

If otherwise, you may need an dynamic input tool in order to input all your data.

mrich93
7 - Meteor

Got it! It's actually YXDB file. So I'm trying to mimic your workflow. Getting stumped here on cross-tab and mutli-field formula. If I can't separate the separate entities if I use an input tool. Where do I specify the two distinct Entities to cross-tab to Multi-field formula? Does that make sense? 

 

mrich93_0-1575487647093.png

 

mrich93_1-1575487678292.png

 

Thanks!

JulioMO
9 - Comet

Hi @mrich93 

 

I am not sure I follow. In order to input your data (.yxdb) you should use an input data tool.

 

Also, the first cross-tab tool is use to separate each entity into new columns. It will have the following configuration: 

Capture.JPG

 

Right after that, the mutli-field formula is used to add 0 to all the field that are 0 in the columns you select (in this case, you will select the Entity columns).

 

Then, each individual cross tab tool is used to get the look that you want as your output data. In this workflow, you will deal with one entity at a time.

Labels