How to combine the Dynamic Select and Summarize tools?

Question

Hello,

I would like to use the Summarize tool to calculate the max value from series that are grouped by Type and Set first with all the records for each group, and then by dropping the first record of each group from the calculation (see attached data set as example).

Moreover, I would like to do this for a dynamic number of series without having to re-select the new ones manually in the Summarize tool, i.e. by using the dynamic select tool to work only with "numeric" fields/columns.

Thanks in advance for your help.

Best,

E

Data.xlsx

danilang · Accepted Answer

Hi @emmanuel_varesio

This was a fun one.

Any time you come across a situation where you have dynamic columns, the Summary tool is not enough to give you a solution without having to reconfigure it every time the columns change.  This is because the summary tool doesn't have the ability to handle dynamic columns.  The way you get around is by transposing the data so that all the dynamic data gets grouped into 2 columns Name and Value.  Once your data is in this format, the Summary tool will easily give you all the summary info you need.

The very top branch that splits into two after the Transpose calculates the max values for N rows and N-top 1 rows, and adds an offset so the final data shows up in the right order.  After that its a question of unioning the records together.  The bottom most row formats the output to be as close as possible to your requirements given that Alteryx doesn't support the concept of spanning columns.

The workflow is dynamic in that you can add any number of Serie_XX columns as you like and any number of data rows which don't have to match by Set and Type, i.e. A-STD can have 3 rows and B-QC can have 25.  A you can see, I added a Serie_03 column to your data.

Dan