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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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
17 - Castor
17 - Castor

@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 Alumni (Retired)

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
17 - Castor
17 - Castor

@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