Making Alteryx pick all numeric fields in the summarize tool

[Bruce123]

Hello

I am doing some data blending and with the way that my data is structured I have to manually pick some variables when I reach the summarize tool. Here I have to pick all the numeric variables.

Is there a way to tell alteryx to always automatically choose all the numeric fields?

The numeric fields consists of data for each month, so when I run the program every month a new numeric field/month will exist in the data and this is where I would like alteryx to automatically add the new month (and thereby having all numeric fields) to the summerize tool.
Is this possible, maybe with a macro or similar?

I can't seem to find a solution for this.

I am quite new to alteryx so this might me a simple question for some.

All help will be appreciated.

Regards

Bruce

[MarqueeCrew]

@Bruce123,

To select all numeric fields (automatically), I would use the Dynamic Select tool (https://help.alteryx.com/10.6/index.htm#DynamicSelect.htm?Highlight=dynamic select).  If you want to group by a non-numeric key and you select all numeric values, then you might need to be creative in how you solve for your problem. 

You also said that you go into a summarize tool after your selection.  If your data gets "new" columns added between runs and you want to perform summary functions on those new columns (automatically) you might need to Transpose the data.  This way, all of your data looks like:  Key+Name+Value.  The Key is optional.  Essentially, you have Name  and Value where the Name is the column name and the value is the cell value.  

This should get you going, but you might have more questions.  If you do, please do ask.  Maybe you could provide some sample (or mock) data and let us know about the desired output.

Thanks,

Mark

[Bruce123]

Hello.
Thank you for replying.

With regard to the summarize tool I was hoping that I could make Alteryx select all the numeric fields in the tool itself. In the tool you have to the option to click "select" and the choose "numeric" (see attachment). As can be seen in the attachment I have one "GroupBy" variable (which will always be the same) and the rest are numeric variables, which I want to sum. So, I was hoping to make Alteryx automatically add all the numeric variables to the "actions" section, since one new numeric variable will come next month when the data is ready, e.g. 201608.

Is there any way to make Alteryx do this? Sorry if I had not made this clear in the previous post.

If this is not possible, then I will try your suggestion to transpose the data. It seems possible to it that way.

Regards

Bruce

[MarqueeCrew · Accepted solution]

Bruce,

Here is a workflow that might help you.  If you replace the TEXT INPUT tool with your data, it will create two (2) versions of your summarized data (vertical and horizontal).

The vertical data will look like this:

The horizontal data will look like this:

The way that I've constructed the data was to:

1. Eliminate fields not used
2. Keep 'Trigger' field as a key in 1 stream of data
3. Keep known (double) values + unknown (*) checked.  The unknown checked will bring in new columns of data if present.
4. Dynamically Select "DOUBLE" elements from the 2nd set of data (#3).  You will probably want to allow all numeric data types.
5. Join the data together (based upon record #).  I did this to clearly show you that the dynamic select was keeping only the data types that you care about.
6. Transpose the data into "Trigger" + "Name" + "Value" records.
7. Sum the values by Trigger + Name.

If you run this module with 356 columns or 3,560 columns of data it will provide you with the "Subtotal" of the day's values with a row for each "Trigger".

If you add a non-numeric column (or a column that contains a character not in {0123456789.} that column will be skipped.  With the data that you provided in your screenshot, there shouldn't be any problems.  If your field name contains a 'SPACE', the results that the workflow will return will rename the field name with an underscore '_' replacing the space.

Please let me know how this works for you.

Thanks,

Mark

[Bruce123]

Hello.

Thank you for your detailed answer.

I found that summarizing a bit eariler in the process and then using your idea with the dynamic select, transpose and crosstab worked perfectly.

So, thank you for your time and help :)

Regards

Bruce.