Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Dynamic Filter

CarlosA
Alteryx
Alteryx
Created

Filtering data is probably one of the simplest Alteryx functions, but it can become time consuming when building the expression, especially when filtering on a larger number of values. Wouldn't it be nice to be able to feed a list of values to Filter your data? I'll show you how you can do this with a simple batch macro.

 

Batch Macro: Setup the Filter tool within your Batch macro with a simple "In" statement, then use the Control Parameter to update the Value within the expression.

 1.png

2.png

 

Connect the Control Parameter to the Filter tool, configure to update the Value within the expression as shown below, this will update that portion of the expression with the column the user defines in the macro.

 3.png

 

Building the Expression:

In a new workflow, Input the list of values you would like to use to Filter your data.

 4.png

 

Connect your data to a Summarize tool, configure the Summarize tool to Concatenate your values into a single column using "," as the Separator and " as the Start and End.

 5.png

Drag out a Formula tool in front of the Summarize tool, then lets add the  parenthesis as show below to complete the expression.

 

6.png

 

Filter:

 

Add your batch macro into the workflow by right clicking on the canvas and selecting Macro, you will need to browse to the location where you saved. Then connect the Formula tool to the "¿" of the Batch Macro. Input the data file you would like to filter and  connect to the remaining Input of the Batch Macro.

 7.png

 

Finally, click on the Control Parameter tab and select the column to be used as the Control Parameter. We are not using the Group By function for this particular example, therefore there is no need to configure.

 10.png

 

See attachment.

Attachments
Comments
dougjsmith11
5 - Atom

This just saved me hours of...change filter, run, change filter, run.   Thank you!!

Gaby
6 - Meteoroid

Greetings!

 

I have been trying to use this macro but I keep getting 2 errors:

 

Record#1: Tool#12: Parse Error at char(11): Parse Error

The output connection "Output6" was not valid. Can anyone explain what it means?

 

The column I am trying to filter is called table_id, the file is csv and the table_id has the same id for many rows.

I want to filter table id=1 and run my workflow, then filter table_id=2 and run until table_id = 10.

 

Thank you for your help!

 

bfuchs
5 - Atom

Hello!

 

One supplemental question, if I wanted this to run, but then I wanted something to happen to the output in an iterative/batch way.

Should that action happen in the macro, or in the main workflow?

 

For example, in the example above, say there is also a person's name, and a date associated with a zip code (i.e. this document tracks a zip code, a salesperson, and the date they were in that zipcode).

then say I wanted to output the two most recent dates and the salespeople in that location (summarize date values, sample two most recent dates, center join original data with those two dates as keys).

 

Should these steps be taken in the macro, or in the original workflow after the macro?

I am afraid that if they take place in the workflow, I will end up with the two most recent dates for all zipcodes, rather than per-zipcode.

 

Let me know if I can clarify, thanks for your help!

dehret001
5 - Atom

Out of curiosity, why choose a macro to do this? Couldn't you just add the text input to the existing data stream, concatenate as shown, and then filter using a formula? Something along the lines of "contains([Concat_ZIP],[ZIP])". 

Dynamic Filter Update.JPG

bfuchs
5 - Atom

Hmm, I'm not sure if that would allow me to compare the two most recent on a per-zipcode level (the overall function is to compare, rather than combine).

dehret001
5 - Atom

It seems to me (and it's possible that I am confused) that the current purpose of the macro in the listed post is to update the formula in a filter "live". All I really think that I've done is replace that functionality with in-workflow tools. For your issue, it sounds like you could use additional filtering tools with dates as your filtering criteria to get the output you're looking for. Could be upstream or downstream of the current filtering tool depending on your priorities and data sources. 

brando2223
5 - Atom

Does this only work in 2019 version? My company is using 2018.3 and the configuration options on the batch macro are different (i.e. instead of a Control Parameter tab there is only a Questions tab that lists all fields in the input file)

esasmaz
5 - Atom

ss.jpgHello Alteryx Community, I'm new to Alteryx. I am attempting to create a dynamic filter. I am trying to get a same answer using this example. However, although I follow the same paths, I get two errors.These errors are "Error: New Workflow2 (28): Record #1: Tool #4: Parse Error at char(8): Unmatched (" and "Error: New Workflow2 (28): RecordInfo::CreateRecord: A record was created with no fields." I don't know what these errors mean. Would you help me with this topic?  

TheAero
5 - Atom

Hello CarlosA,

 

I've been trying to implement your solution using a batch macro to dynamically filter a list. However, when I try to run it I always receive two errors: 

TheAero_0-1583930128833.png

Could you help me out here? It would really save me some time if I could run this dynamic filter tool instead of updating the filter every time.

Here you can see the lower input

TheAero_1-1583930300845.png

and the upper input

TheAero_2-1583930486546.png

 

Unfortunately, it doesn't let me upload the macro.

Thank you very much for your help, I appreciate it.

Lijuan_Guo
5 - Atom

Thank you for sharing this workflow , It inspired me to create a batch macro to dynamic select the columns which I want to include or exclude.   Really appreciate!

DataMeister
7 - Meteor

CarlosA: thanks for all your hard work but it's wrong for Alteryx to make users do this from scratch. Every app I use has filtering, even Excel. They all automatically list the unique values of every column in the dataset. ArcGIS does this in its SQL-based Query Builder window even though it takes some clicking to see the values. Once they load, in one dataset I see hundreds if not thousands of values to pick from--and ArcMap lets us type to narrow down the list. Tableau's filters offer the values, besides options to show them in a pulldown, radio button list, multiple selection list, and more. An ancient geophysical program called Petra also has various filtering options though it's practically 1990's technology. My point is that otherwise-sophisticated Alteryx could do the same if not for this apparent blind spot. Please contact me for examples and screen-sharing if the business case still isn't clear. Common sense filtering should've been available in Alteryx from the beginning.