Multiple Filtering and Tokenise

[SH_94]

Hi Community,

Good day to you all,

I would like to build the workflow which able to filter multiple column and perform the tokenise action on certain column. Below is the original table and result that i wanted.

Below are the my plan to build the workflow and would like to know how you all build the workflow so that it is more dynamic.

1. I would like to filter those GL column that starting with 14 series and 15 series account. 

2. Once it is done, I want to filter the source column with the wording " RU" . Subsequently, i want to filter the category column with the wording "TR".

3. After the above steps are performed , i would like to perform the text to column on the concatenate column whereby it will split into the result into country , company and type as per screenshot below.

However, if there is symbol  of "&" in the type column , i would like to take the original Type column information. In this case, it show the result with the word "C" under the column Type.

I am thinking of whether i want to use formula tools or filtering tools to build the workflow as per above plan. I notice that some people will use formula and some people will use filtering tool in certain cases. May i know how you all decide whether you want to use formula tools or filtering tool when you all build the workflow.

May i know in this case, is it possible to build the workflow without filtering tools and only formula tools? 

Appreciate if you can share the workflow so that i can make the reference on how you build the workflow more dynamicly.

Thank you and happy weekend

[Sntrada · Accepted solution]

Hi Jacob, 

I have attached a workflow that transforms the data to the ideal form. 

I don't believe you can make rows go away using the formula tool, the filter tool is the most appropriate for this. For more complex/multiple criteria filtering you need to write a formula within the filter tool. In this case, I only used one filter, but with multiple conditions. 

If you have any questions about any part of the workflow, let me know and I'll elaborate. And if you want a full walkthrough, I can record my screen and do an explanation. 

Cheers!

[SH_94]

Hi @Sntrada ,

I just finished going through your workflow and quite impressed on how you build it. I had bad practice that i always try to reduce the number of tools in the workflow and sometimes i will confuse between formula tools and filtering tools. Because there are some similarities when come to filtering function.  Today i just know that it is quite impossible to build the workflow with just formula tools in this case and thank again for your advice.

I have two queries which would like to clarify with you as below.

1. May i know what is the reason behind that we add the select tools at the beginning.

2.May i know in what circumstances that normally you will go to filter tools instead of formula tools and vice versa?

Thanks again for your help and happy weekend.

[Sntrada · Accepted solution]

Hi Jacob,

Glad you found it useful!

To answer your questions: -

1) I used the select tool in the beginning because the GL variable was imported as a numeric variable (double), but we need it to be a string variable to do the filtering operations. 

Using the select tool, I changed the variable type to string:

The filter tool gives different options for numeric and string values. For numeric values, we get these options:

For string, we get these ones:

In this case, we need the GL to be a string value, so the designer can evaluate if the strings we are looking for "14" and "15", are at the beginning of the GL cell in each row. If we left it as numeric, designer would be looking for GL accounts which are =  the numbers 14 or 15 exactly, so it won't pick up 1400 or 1500, or 1401, etc. 

Hope this makes sense.

2) I always use the filter tool whenever I need to filter out rows of data. The formula tool does pretty much everything else, that doesn't involve removing rows of data. The formula tool is super powerful and versatile, and you can do pretty much any formula you would do in Excel with it, and then some. You can check out these resources to learn more about the formula tool: 

Formula Tool | Alteryx Help

Tool Mastery l Formula - Alteryx Community formule formel

Here are some resources for the filter tool:

Filter Tool | Alteryx Help

Tool Mastery | Filter - Alteryx Community