Hi All, I have below records and I need an additional column which tells me the sum across row level. Request your help in resolution. Data: A121414512664B216765133643C3211235479963D53234856541352E123347485495758 Output required: A121414512664235B216765133643812C3211235479963676D532348565413521091E1233474854957582100

Sum across row level

Hi All,

I have below records and I need an additional column which tells me the sum across row level. Request your help in resolution.

Data:

A	12	14	14	5	126	64
B	21	67	65	13	3	643
C	321	123	54	79	96	3
D	53	234	85	654	13	52
E	123	34	74	854	957	58

Output required:

A	12	14	14	5	126	64	235
B	21	67	65	13	3	643	812
C	321	123	54	79	96	3	676
D	53	234	85	654	13	52	1091
E	123	34	74	854	957	58	2100

Transformation

Accepted answers

jrgo

Here's a couple options to do this. The 3rd option, not included, is of course to to just use a Formula to add up each field which may be OK if you always have the same amount of fields to add up.

Note that in the Cross Tab tool (first option), there's 2 "Method for Aggregating Values" options selected.

shikhar6339.yxmd

michelle_mathews

Hi @shikhar6339 , two ways this might be accomplished. Example attached!

Screenshot 2023-09-05 130922.png

Row Level Sum.yxmd

All comments

jrgo

Here's a couple options to do this. The 3rd option, not included, is of course to to just use a Formula to add up each field which may be OK if you always have the same amount of fields to add up.

Note that in the Cross Tab tool (first option), there's 2 "Method for Aggregating Values" options selected.

shikhar6339.yxmd

michelle_mathews

Hi @shikhar6339 , two ways this might be accomplished. Example attached!

Screenshot 2023-09-05 130922.png

Row Level Sum.yxmd

adamweaver39

I think this would be easiest to resolve with a simple Formula tool where you add up each Column!

Otherwise, you could use the Cross Tab and Summarize tool.

caltang

I would go with Transpose tool then Cross-Tab tool to ensure that it is dynamic. Because it accounts for future fields/columns that you may need, and it is flexible enough to ignore dropped fields if it changes (change the Transpose tool setting at the bottom of its configuration pane on the left).

That way you get your horizontal sums dynamically all the time. Refer to @michelle_mathews ' second solution in their attached workflow.

Qiu

@shikhar6339
I also agree with @caltang @michelle_mathews approach, which is dynamic.
We do need to ntoe that there is a drawback is the name of columns might change after Cross Tab by adding some underscore.
We can use Dynamic Rename tool to bring them back.

shikhar6339

Thank you all. I tried using summation of columns via formula but wasn't getting the desired results. It was because some of records had null values. I replaced nulls with 0 and then summation worked.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335