Formula tool vs. Data Cleansing tool: best use?

[LMR396]

Hi Alteryx Community,

I had a question about using the Formula tool to cleanse data vs. using the Data Cleansing tool. Are there times when one is more efficient than the other? For example, is the Data Cleansing tool a better option for removing whitespaces whereas the Trim formula is better for a task such as removing leading digits? It seemed to me that because the Data Cleansing tool is not dynamic, the Formula Tool is a better option because you can complete the same tasks with slightly more functionality.

I’m new to Alteryx and am still learning a lot.  I’m enjoying the process and am trying to understand best practices when creating workflows.  Thank you!

[Simha]

Just a few points:

- Data cleansing comes with a set of widely used/ needed cleansing functionality that you can readily without writing a single line of code. More features might be added to this tool in the future as the community continue to identify and suggest new ideas.

- Formula tool should be used when the functionality you are looking for (for example Trim as you mentioned, or standardize date formats etc.) is NOT available in the data cleansing tool.

To summarize, you should first look to use Data cleansing tool when possible, only when your needs are not met should you utilize Formula tool.

Hope this helps.

[fmvizcaino · Accepted solution]

Hi @LMR396 ,

Actually, the Data Cleansing tool is a set of multi-field formula tools serialized as image below.

Each Multi-field formula represents a check box in the data cleansing tool and all of them are developed in the same exact way you would in your formula tool. 

You should use the Data Cleansing tool if you want to develop something faster, but keep in mind that for large datasets, the data cleansing tool will take way longer than your formula tool.

Lets say that you want to trim spaces - with the data cleansing tool, only one formula tool would be 'activated' and all the others would be useless, but the data would have to pass through 8 more formula tools waisting a bit of time.

Summarizing, if you are not concerned about performance, it is more practical to use the data cleansing tool.

obs: you can see inside the data cleansing tool by right clicking in the tool and selecting to open macro.

Best,

Fernando Vizcaino