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

Alteryx designer Discussions

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

Dynamically select columns in Summarize Tool

Highlighted
Asteroid

Hi All,

 

I have an incoming data stream that has different number of columns for every different ID that I run - I wish to do 2 things with it:

 

1. Dynamically rename the fields to a certain set standard/naming convention

     EG: content_id to Content ID 1,  content_id_2 to Content ID 2, and so on.

 

2. Dynamically select all the columns and then do a Max(Column Name) in the Summarize tool.

 

Currently, I have to keep changing the workflow to accommodate this varying number of columns. Any help in this regard would be helpful. Thanks.

 

 

Alteryx Certified Partner
Alteryx Certified Partner

@sagar_agarwal,

 

I have what I think is an easier solution to your challenge.  You can use the TRANSPOSE tool followed by a SUMMARIZE tool.

 

The transpose tool will read all data and convert it into NAME + VALUE (or key plus name plus value) pairs.  Imagine that your data has field 1, field 2, ... field n.  The output from the tool is:

 

NAME, VALUE

Field1,10

Field2,20

FieldN,30

Field1,11

Field2,15

FieldN,30

 

You can then summarize with a constant configuration of Group by NAME and MAX of Value.  The output would appear as:

NAME,MAX_VALUE

Field1,11

Field2,20

FieldN,30

 

You could crosstab that if you want to get

Field1,Field2,FieldN

11,20,30

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

Thank you @MarqueeCrew

 

While this is indeed a simple solution and I will be implementing this, could you please help me with an alternate solution using the dynamic select tool, too? It will be an important learning point as I often run into cases where I have to dynamically select columns and pass to several tools such as the XML Parse, or the Text to Columns, etc. Currently, I hard code the column names which I feel is not very efficient and robust.

 

Thanks! :)

Alteryx Certified Partner
Alteryx Certified Partner

To dynamically rename a column name, you would use the DYNAMIC RENAME tool.  If you want to replace all underscore characters with a space, I would use a formula like:

 

regex_replace([_CurrentField_],"_",' ')

To dynamically setup a Max(column name) value in the summarize tool, I would still use the approach of using a TRANSPOSE tool.  There is a data investigation tool, Field Summary which will provide metrics on your input data and will provide you with the maximum value of every column.

 

Once you rename the fields and run the field summary, maybe this is all that you need.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

Works great!  Except when the field names contain spaces.  The Cross Tab tool replaces the spaces with "_" and therefore messes up the field names.

 

Gary

Labels