Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Summing data from Multiple columns into a new calculated column

Highlighted
6 - Meteoroid

Hello friends, 

 

What is the best way to sum values from multiple columns, from an Excel file, into a new column? If it is an expression, could you please show an example? 

 

Thank you!

Highlighted
13 - Pulsar

I would use the Formula tool. Place it after your Input Data tool, then from the drop-down in the Formula tool's configuration select "+ Add Column" and type the name for your new column. For the expression, use a formula to add them together:

 

image.png

Make sure to choose an appropriate Data type (i.e. Int16/32/64, Float, Double, etc) so you can use it later in your flow.

 

Hope this helps!

Dan

Highlighted
6 - Meteoroid

I also have a similar question...I currently have 10+ columns that I would like to sum up. Is there a way to do it without having to manually put in each field name into the formula tool? 

Highlighted
13 - Pulsar

Depending on your data structure, you could use a Transpose and then a Summarize.  This would allow you to select the fields you want to keep and sum the rest together.

Highlighted
6 - Meteoroid

Thanks guys!! All excellent solutions. I will try them out and see which one works best for me 

Highlighted
8 - Asteroid

I have a similar requirement with more than 100 columns that i need to sum up. But i do not want to do a transpose & summarize. Is there any other way of doing it efficiently?

Highlighted
Alteryx Certified Partner

Hi @GaRaGe,

why you don't want to use transpose?  I think you want to see your sumed field along with other 100 fields, for that you can use join tool and achieve the desire result. i am attaching the solved workflow. See if it helps.(As an example I am taking 20 fields, you can do this same on any number of fields)

Screenshot (131).png

Thanks,

Bharti

Highlighted
8 - Asteroid

thanks @Bharti but wont it be more complex when the number of fields and rows are really huge in number?

Highlighted
Alteryx Certified Partner

@GaRaGe According to me ,this is the most simple method when the number of rows and fields are more. Try it out, you will get to know. Thanks.

Labels