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

Question

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

Dynamic Summarize.yxwz

Pilsner · Answer

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 
.

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

.

Dynamic Sum.yxzp

Erin · Answer

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!

MudgilNiket · Answer

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.