i have files with 106 fields, all numeric. i want to report the MIN, MAX and AVG for each field, in 3 rows. if it do this in one summarize, i get 106*3 fields, instead of 106 fields with 3 rows. is it possible to "layer" row 1 to be the MIN, for each field. row 2 to be the MAX, and row 3 to be the AVG, for each field? thanks
becki kain
Solved! Go to Solution.
Many ways I'm sure...first that comes to mind for me would be:
1. run the data to three separate summarize tools (one for each agg function)
2. add a column to each with the summarize type ("MIN","MAX" or "AVG")
3. use dynamic rename tool to change all the summarized column names back to the originals using the "Take Field Names from Right Input Rows" option. (you could hand rename all 106 in the summarize tool 3 times, but who would want to do that?) https://help.alteryx.com/11.3/index.htm#cshid=DynamicRename.htm
4. union the outputs
I would probably propose something a bit different.
I would use the field summary tool to bring together your aggregates (this automatically pulls through min, maxes and averages for your selected fields, among other metrics you may be interested in).
You can then pivot your data, so you get a line for each measure and metric.
Before finally cross-tabbing into your desired state.
I've attached the workflow shown in the example.
Ben
thanks. I had thought about that idea, but with a select, not dynamic rename but I was stuck on the union part. :-)
union or join, the outputs?
union was the way to go, thanks!
If you're asking about the method I suggested, union.
That's why you want all the columns to be named identically after the summarize so you can "stack" the values in the output as desired.
Nice solution Ben!