Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Macro filter with listbox

rtlane
8 - Asteroid

Hi,

I'm trying to make a macro that has a list box with 4 options for filtering a single column from an excel spreadsheet input. I can set it up to filter one at a time by using the contains function: 1 or 2 or 3 or 4. The issue i'm having is figuring out a formula for a combination of the 4 options. For example if I want to filter for 1 and 2. 

 

10.PNG

 

20.PNG30.PNG

8 REPLIES 8
patrick_digan
16 - Nebula
16 - Nebula

@rtlane There are a number of ways to accomplish this. The way I normally do it is to bake my formula into the list box tool. In the start text box, I would write

 

Contains([Technology],"

The Separator box would be 

 

 

") OR Contains([Technology],"

and the end text box would be 

 

")

This is a simple way because you wouldn't have to add any tools or write a strange formula into your action tool.

EDIT: You would then need to change your action tool to replace the entire contents of your filter instead of just the 1 string. You would just uncheck the box.

scottj
Alteryx
Alteryx

You can use an IN statement and then replace a specific string.

 

Here is the Filter formula

[a] in ("1","2","3")

FilterIN.png

 

and then in the Action tool replace the field names and quotes you are creating.

 

boxselectIN.png

 

Scott Jones
Strategic Sales Engineer
Alteryx, Inc.
rtlane
8 - Asteroid

Thank you, that worked like a charm

rtlane
8 - Asteroid

thanks Scottj. For some reason I was unable to get that to work before. My filter options are not exact and that's why I needed the contain function. I tried to combine the IN function and the CONTAINS function, but it did want to accept it. 

patrick_digan
16 - Nebula
16 - Nebula

@rtlane For future reference, you could also use the macro @MarqueeCrew created which essentially combines the contains and in functionality. It would look like this where his macro would replace your filter. This would allow you to pass a comma separated list to his macro:

 

Capture.PNG

 

rtlane
8 - Asteroid

Thank you patrick.

 

I added a feature where the the user can select the fields from the select tool. Is there a way I can make it where there is a unique tool after the select tool and the unique tool is updated with the same fields that was selected in the filter automatically?

For example:

User selects the fields: A, B, C - in the select store

Unique tool is updated with the same exact selections

 

50.PNG

rtlane
8 - Asteroid

Depending on which selections are made in the select tool, I want the unique tool selections to match the select tool. Using the example below, if I remove the column "phone numbers" using the select tool and just leave "Name" selected, then I would want the unique tool to also just have "Name" as the selection. Leaving me with a column with 2 records, Jane and Jill.

 

NamePhone Numbers
Jane555-555-5555
Jane111-111-1111
Jane222-222-2222
Jill777-777-7777
Jill888-888-8888
rtlane
8 - Asteroid

I figured it out. Just did as shown below and it will automatically update the unique tool from the list box. Selected "Updated Field List" as the action type.

 

 

2020.PNG

Labels