Calculate Sum, Count & Average of all field without using Summarize tool

[hemant86]

Hi All,

I have a dataset which has close to 800 columns. I want to calculate the Sum, Count & Average of each field. I'm able to do it using Summarize tool but the problem is that the dataset can have additional fields or previous fields might get removed in future. So in this scenario the Summarize tool fails since it gives error for missing fields.

Transposing the entire data and then using Summarize is an option but since there are 800 fields so the volume of data is huge and transposing the data takes lot of time(close to 15min to execute). Not sure how to go around this. Appreciate any help around this.

Thanks in advance.

[binuacs · Accepted solution]

@hemant86 use dynamic summarise for your use case

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Dynamic-Summarize/td-p/64335

sample workflow attached

[Qiu]

@hemant86 
It is to me that the Transpose the data to vertical format then apply Sum/Ave is the good way to go.

About the performance, have you tried to turn on the AMP engine to make it abit faster?

[Yoshiro_Fujimori]

Hi @hemant86 ,

If you just need the Sum, Count & Mean of all fields, Field Summary Tool may work.

https://help.alteryx.com/current/en/designer/tools/data-investigation/field-summary-tool.html

It provise a variety of statistic information of the input data.

I hope this helps. Good luck.

[Qiu]

@Yoshiro_Fujimori 
I have not used this tool. thank you !

[hemant86]

Thank @binuacs This should do the job for me. 😊

[hemant86]

Hi @binuacs I was checking the macro and and do not see where you have taken the option to populate Count & AVG. How do I modify the macro to count only NOT Null values.

[binuacs]

@hemant86 what you have to do is check the xml part of the summarise tool and how each action given there, then copy the action and paste in the second input text tool

[hemant86]

Thanks @binuacs I learnt something new today. Now I can use Dynamic Summarize for all kind of calculations.🙂