This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Multi-Field Formula Tool onour way to mastering the Alteryx Designer: Tool Introduction The Multi-Field Formula Tool offers the same functionalityas the Formula Tool, but offers the added benefit of applying a function across multiple fields of data all at once. Gone are the days of writing the same function for multiple fields. Sample Explanation Say there are four fields with dollar signs ($) that need to be removed. A sample of the data looks like this: It could be done with a Formula Tool and a function written for each field: But doing so would have some downsides: * We know the data type should be numeric, but changing it with the formula keeps it in its original data type format. This means that it is not dynamic enough and we will need to add more controls thereafter. * Although the formula tool does the job well, it is still hardcoded to do it. Imagine having new fields coming in that are numeric in nature and will also have the $ sign, the formula tool will need to be configured at each turn whenever a new field is in. * Writing so many expressions may seem okay with a few fields, but once you have tens, hundreds, or even thousands of fields, the formula tool is no longer scalable or sustainable. So how do we counter the downsides mentioned above? We write it once in the Multi-Field Formula Tool: Before: After: This will save time if any edits are needed and keep workflows a little cleaner. It is also dynamic and can allow you to create new fields or change the data type / size too! Tool Deep Dive: * Selections: Make sure the correct field/data types are selected for the function being performed (e.g. string functions being applied to string fields). This allows you to select all relevant fields and allows you to select Dynamic or Unknown fields that may come later - thereby allowing greater flexibility in preparing the data. * Create New Fields: By default the ‘Copy Output Fields and Add’ check box is selected. This keeps the original fields and adds the output data as new fields at the end of the dataset with the specified prefix or suffix. This is useful if you do not intend to change the original field's data type, size, and content. * Change Data Type: The output data type can be changed using the ‘Change Output Type’ check box. When using this option keep the resulting data type of the function in mind and make sure it matches the selected output data type. In our example, we can clearly see that the fields of interest are numeric in nature - so it makes sense to convert them to a numeric data type in one go rather than having multiple tools to do it. * Choosing Variables: The variables under 'Current Field' will use the fields selected in section 1 in the above image. This allows it to be flexible as it can change user defined constants as well, thereby making it more adaptive to the developer's needs. It also allows for the dynamic field selection and it allows for greater flexibility with current fields / exact fields selection. * Expression: Just like your formula tool or any tool with an expression box, it allows the developer/user to define the transformation needs to make the data useable. For our example above, using [_CurrentField_] in the expression will use the selected field names individually in the function and transform each one the same way. You can also reference the field name or data type in your formula by using _CurrentFieldName_ or_CurrentFieldType_, respectively. This allows for greater expression capabilities than other tools, and when used with other tools, will be a solid plus to your workflow's dynamic status. Use Cases: Use this tool for the same use cases as the Formula Tool with the added flexibility of applying the same function across multiple fields at once! Update fields, perform calculations, cleanse data… In the attached example 3 Multi-Field Formula tools are used to update 8 fields! (The example workflow is compatible with Alteryx 10.5 and later.) By now, you should have expert-level proficiency with the Multi-Field Formula Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series. Stay tuned with our latest posts everyTool Tuesdayby followingAlteryxon Twitter! If you want to master all the Designer tools, considersubscribingfor email notifications. Multi Field Formula Examples .yxmd MultiFieldFormula_WF Example.yxmd

Tool Mastery | Multi-Field Formula

This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the Multi-Field Formula Tool onour way to mastering the Alteryx Designer:

Tool Introduction

The Multi-Field Formula Tool offers the same functionalityas the Formula Tool, but offers the added benefit of applying a function across multiple fields of data all at once. Gone are the days of writing the same function for multiple fields.

Sample Explanation

Say there are four fields with dollar signs ($) that need to be removed. A sample of the data looks like this:

It could be done with a Formula Tool and a function written for each field:

But doing so would have some downsides:

We know the data type should be numeric, but changing it with the formula keeps it in its original data type format. This means that it is not dynamic enough and we will need to add more controls thereafter.
Although the formula tool does the job well, it is still hardcoded to do it. Imagine having new fields coming in that are numeric in nature and will also have the $ sign, the formula tool will need to be configured at each turn whenever a new field is in.
Writing so many expressions may seem okay with a few fields, but once you have tens, hundreds, or even thousands of fields, the formula tool is no longer scalable or sustainable.

So how do we counter the downsides mentioned above? We write it once in the Multi-Field Formula Tool:

Before:

After:

This will save time if any edits are needed and keep workflows a little cleaner. It is also dynamic and can allow you to create new fields or change the data type / size too!

Tool Deep Dive:

Selections: Make sure the correct field/data types are selected for the function being performed (e.g. string functions being applied to string fields). This allows you to select all relevant fields and allows you to select Dynamic or Unknown fields that may come later - thereby allowing greater flexibility in preparing the data.
Create New Fields: By default the ‘Copy Output Fields and Add’ check box is selected. This keeps the original fields and adds the output data as new fields at the end of the dataset with the specified prefix or suffix. This is useful if you do not intend to change the original field's data type, size, and content.
Change Data Type: The output data type can be changed using the ‘Change Output Type’ check box. When using this option keep the resulting data type of the function in mind and make sure it matches the selected output data type. In our example, we can clearly see that the fields of interest are numeric in nature - so it makes sense to convert them to a numeric data type in one go rather than having multiple tools to do it.
Choosing Variables: The variables under 'Current Field' will use the fields selected in section 1 in the above image. This allows it to be flexible as it can change user defined constants as well, thereby making it more adaptive to the developer's needs. It also allows for the dynamic field selection and it allows for greater flexibility with current fields / exact fields selection.
Expression: Just like your formula tool or any tool with an expression box, it allows the developer/user to define the transformation needs to make the data useable. For our example above, using [_CurrentField_] in the expression will use the selected field names individually in the function and transform each one the same way.

You can also reference the field name or data type in your formula by using _CurrentFieldName_ or_CurrentFieldType_, respectively. This allows for greater expression capabilities than other tools, and when used with other tools, will be a solid plus to your workflow's dynamic status.

Use Cases:

Use this tool for the same use cases as the Formula Tool with the added flexibility of applying the same function across multiple fields at once! Update fields, perform calculations, cleanse data…

In the attached example 3 Multi-Field Formula tools are used to update 8 fields! (The example workflow is compatible with Alteryx 10.5 and later.)

By now, you should have expert-level proficiency with the Multi-Field Formula Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at community@alteryx.com if you’d like your creative tool uses to be featured in the Tool Mastery Series.

Stay tuned with our latest posts everyTool Tuesdayby followingAlteryxon Twitter! If you want to master all the Designer tools, considersubscribingfor email notifications.

Multi Field Formula Examples .yxmd

MultiFieldFormula_WF Example.yxmd

Dynamic Processing

Preparation

Tool Mastery

Accepted answers

All comments

andrew_cook

Hello Peter,

Thanks for this. Your post is very helpful. I believe I have a problem that could be solved with this tool, however I'm not sure how to apply it. I have a data set containing an employee hierarchy. Basically, I follow the reporting structure from top (CEO) to bottom (employee's direct supervisor). Each row is an employee who could have 1-11 levels of leaders between them and the CEO. All other level fields are NULL. I want to identify the first NULL value in each row (which could be in any column) and replace the null with the employee's ID. Will this tool help or is there a better way?

Thanks much, Andy

PeterS

Hi @andrew_cook,

The Multi-Field Formula tool probably won't be the best tool for what you are trying to accomplish. I have come up with an example that I believe demonstrates what you are trying to do, however, I can not attach a file to responses on this post so keep an eye out for an email from support@alteryx.com with the example.

Peter

andrew_cook

Thank you Peter. This is exactly what I needed!

Regards, Andy

PeterS

You are welcome @andrew_cook Thanks your your involvement with the Alteryx Community! If you don't find an answer to future questions feel free to post it in one of the discussion boards or send us an email at support@alteryx.com

Have a good one,

Peter

M_G

A new user of Alteryx, well versant with MS Access. Just trying to do some simple aggregate queries in Alteryx but can't seem to find what would be equivalent to 'Where' clause in Access.

I have a fairly large table and I'm trying to get count of Rpt_No based on certain Report Date cut off. Can't seem to find a way to specify date in the Summarize tool while doing Count on Rpt_No. I'm sure it would be fairly easy if you know how to do it.

patrick_digan

@M_G Check out the filter tool. You can specify something like [Report Date] >'2016-12-01' in order to get just those records with report dates after 12/1/2016. Then you can use a summarize tool to get counts.

M_G

patrick_digan, yes that seems to work. Smiley Happy

M_G

Now that I'm trying to do similar record count on a much bigger table, with Filter and Summarize tools, I run into 'Conversion Error' due to some records with invalid data in the Date filed. And Alteryx just hangs up and does not give me record count. I know there are indeed some invalid dates in the Report Date field and want to ignore those records, as I don't have any control over the data or the database.

cleotyler

I believe I am trying to solve a similar problem to Andrew's. I would like to replace the null values of one column of dates with the dates from another column. Would this be the right tool for that? UPDATE: I was able to solve my problem using the Formula tool.

alexpalermo

Hi Peter,

Would the Multi-Field Formula be the correct tool to use if I wanted to update fields across multiple files?

Say I have 3 files...File 1 has fields 01 to 10 and I want to set their values to "1". File 2 has fields 01 to 15 and I want to set their values to "1" as well. File 3 has fields 01 to 20...

I want to set the values for all fields to "1", but each file has a different number of fields. How do I handle this?

Thank You,

Alex

patrick_digan

@alexpalermo The multi-field formula tool is not currently setup to handle this. You may be able to get the multi-field tool to work by using the dynamic or unknown fields checkbox. If that doesn't work, then: The most used approach in this community would be to transpose your data, run a slightly complicated formula, and then crosstab the data back. The transpose would allow you to specify dynamic fields. My alternative approach is this macro on the Alteryx Gallery which I find more straight forward in that it doesn't involve changing your data structure around. You could use the dynamic field selection and the Name variable to grab the needed fields (perhaps a contains function or a regex_match would work). If you just want to grab all your fields, you could just provide a dummy 1=1 formula. Or if you know the fields you want to exclude, you could manually exclude those and any new fields will automatically be used. Hope that helps!

alexpalermo

Thank You Patrick.

I started with the Transpose\Crosstab method initially but thought the Multi-Field Formula would be more efficient.

I'll go back to that method.

Appreciate your reply.

Alex

paulvu0911

@patrick_digan: is there any macro that allows us to dynamically select fields with a formula like the Multi-row Multi-field macro? For my purpose, I don't need a multi-row formula tool, so I'm not sure if the Multi-row Multi-field macro is the right approach for me.

Thanks!

Dat.

RodLight

@paulvu0911

Check out the Dynamic Select. It allows for selection on the basis of a Formula.

paulvu0911

Sorry I should have mentioned that I wanted to dynamically select fields within the Multi-field Formula Tool. I have a formula that I want to apply to only certain fields, but the number of fields is not fixed.

RodLight

You could do something like this, maybe? Have two Dynamic Select tools with "opposite" conditions in the Formula. Apply your Multi-Field Formula to those you want, and then join them back together afterwards based on a unique ID field (I used a RecordID tool to do this).

Note that I included the RecordID in the one tool to make sure it passed through. Also with the Multi-Field Formula, I did unselect the RecordID.

paulvu0911

That's actually what I'm doing right now to overcome the issues, but I appreciate the response. I just wonder if somebody has created a macro to put the dynamic selection into the multi-field formula tool, so that I don't have to do Join/Union.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703