Input
Co-MSF | a | b | c | d | e |
0001 MSF296 | 5 | ||||
0001 MSF255 | 7 | ||||
0001 MSF296 | 10 | ||||
0001 MSF265 | 11 | ||||
0001 MSF296 | 12 | ||||
0001 MSF255 | 9 |
Required output:
Co-MSF | a | b | c | d | e |
0001 MSF296 | 15 | 12 | |||
0001 MSF255 | 7 | 9 | |||
0001 MSF265 | 11 |
Solved! Go to Solution.
Hi @alteryxintern you can do this multiple way the simplest is using a summarise tool to group by Co_MSF and sum up all of your columns. However this is not dynamic as you would need to specify each column you want to sum. I've come up with two approach that should guide you.
Thanks! JosephSerpis
One more question - if I wanted to add a column at the end to sum (a+b+c+d+e) for each Co-MSF, how can I do that?
I used the formula tool to 'add a new column' and put in the formula 'a+b+c+d+e' however, it is not working, all values come as null un the new column.
Hi @alteryxintern as you mention the value are resulting in null because you are adding nulls in your caculation. You can deal with this in a couple of ways use a data cleanse tool before the formula tool to replace the nulls with 0. You can use a Multi-Field Formula tool to achieve the same affect with an If formula testing if the value is null and replace with 0. Or if you are using a crosstab tool such as I did in option B you can scroll down the method for Aggregating values in the configuration window of the cross tab tool and select total column which will provide that sum column for you.
Can you please elaborate the 2nd method. How do I use it to sum the total aggregate