Help Needed: Summing Multiple Columns Individually Using the Summarize Tool in an Analytic

[MudgilNiket]

Hello Alteryx Community,

I hope you're doing well!

I'm working on a new workflow and need assistance with summing multiple columns individually using the Summarize Tool within an interface frame. I want to allow users to select which columns to sum, resulting in outputs like Sum_A, Sum_B, etc.

I tried using a ListBox for this but didn't have success. The number of columns can vary (2, 3, or 4).

Any guidance or examples would be greatly appreciated!

Thank you!
Niket

[Yoshiro_Fujimori]

@MudgilNiket ,

I would summarize all the columns after the field is selected by List Box Tool.

I hope this helps.

Workflow

Output

[MudgilNiket]

Thank You!! @Yoshiro_Fujimori 

This works great but I have created this as an alternative workflow.

My real goal is to compare 2 files, with an interactive app allowing me to select the columns I wish to sum up individually and compare it with the other file having the same workflow configuration.

[Yoshiro_Fujimori]

If the metadata is identical between the two files, I would

1. get the sum for each column for each file
2. join the two result with the column name
3. compare the sums of the two files.

(If you want to reduce the columns before summarizing, you can apply the same trick discussed before.)

Hope this helps.

File1

Column 1	Column 2	Column 3
1	2	3
1	2	3
1	2	33
1	22	33

File2

Column 1	Column 2	Column 3
1	3	3
1	2	3
1	2	33
1	22	33

Workflow

Output

Column Name	File1 Sum	File2 Sum	Test
Column 1	4	4	TRUE
Column 2	28	29	FALSE
Column 3	72	72	TRUE

[MudgilNiket]

Thank you! However, when comparing 100 different files (two at a time) and multiple columns, we will need to use a list box to select the columns and an action tool that prompts me to update the join with a formula.

If there is any logic I might be missing, please let me know.

[Erin]

Hi @MudgilNiket!

This is an interesting use case. I'm curious, are you set on the Summarize tool? Since there's multiple ways to solve things in Alteryx, a different approach might be transposing all the columns that you would need to sum first, and then using a crosstab to sum them up with the Name as headers and the Value field as the Values for New Columns. Make sure to add a group by on the file name or file path (in the Input tool, there's a Output File Name as Field option). You might need a Dynamic Rename after the Crosstab to clean up field names (replace "Sum_" with nothing). Then you could use the list box option to filter the sums across 100 files and it would handle varying number of columns nicely. 

Another tool that you might want to look into is the Field Summary Tool in the Data Investigation palette. It doesn't have a sum feature unfortunately, but for numeric fields it will tell you the min, max, median, standard deviation, percent missing, count of unique values and mean. Not sure if any of that might also be useful in your comparison or if you just need pure sums of columns. There's not a group by option for this tool, so you might need to put it in a batch macro so that you could have it analyze one file at a time. 

Hope this helps or at least sparks some additional avenues! 

[Pilsner]

Hello @MudgilNiket,

Apprechiate you already have some excelent wokflows from @Yoshiro_Fujimori , but just thought I'd mention how I have sumed columns via a list box in the past:


First of all, I use a list box which feeds into a select tool. This means that only the column I choose in the list box will continue past this point.

 
Next, I used a field info and a formula tool to help me format the underlying XML (the code which powers the Alteryx tools). 

Next, to combine all the different lines of XML into cone cell, I used concatinate from within the sumarise tool, with a delimiter of \n.

Finally, this formatted XML feeds into a batch macro. The batch macro itself is simple as the only main tool is the sumarise. In the action tool, you need to make sure that the controll parameter input will update the inner XML.

I have attached the workflow below but if you have any questions please let me know.

Regards - Pilsner

.