community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to combine the Dynamic Select and Summarize tools?

Atom

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

Highlighted
Nebula
Nebula

Hi @EV 

 

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.

 

WF.png

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. 

 

Results.png

 

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

Atom

Excellent! Thanks a bunch! That's exactly what I need.

 

Still a long way to go to find this solution without your help (I've just started using Alteryx ...) :-)

 

Thanks again.

 

E.

Labels