Dynamic Select - Getting Boolean Logic Right

[caltang]

I currently have 8k+ columns that users will have to decide on via an Analytic App. 

I’ve used the Analytic App to get this working - but I have some concerns on how flexible I can go with the Dynamic Select with formulas.

I require: 

1. Users to select on their own

2. Users to key in values for CONTAINS, STARTSWITH, ENDSWITH, and their NOT counterparts. 

Because of Boolean logic, the AND / OR statements must be selected by end users to achieve their desired selections. 

In addition, the NOT statements can only work well if all fields are selected… because they’re trying NOT to get those fields with certain string patterns. 

I’m trying to make it as dynamic as possible for end user convenience. Should I break down into chunks? 

[geraldo]

@caltang 

If you are going to implement this on an Alteryx server I believe each step you should be testing on the server. In Alteryx Designer the functionality is one on the Server is another for chained workflows and the inputs
I believe that you have already done the previous step that you requested about choosing between the 8000 columns and having a new confirmation screen

[caltang]

Not really following what you’re saying @geraldo - I think my question is more on the dynamic select logic which I wish to implement.

But if you’re saying that it is one thing to build on designer and another on Server, that I am aware. Perhaps a chain app is not the best.

But then again, with dynamic select, having both Contains, NOT Contains, STARTSWITH, NOT STARTSWITH, ENDSWITH, and NOT ENDSWITH is rather conflicting - cannot be both true and/or false. 

That’s my dilemma - because from an End User POV - they’re trying to apply their selections to the raw data itself / sometimes to the changes of their selections too.

Not sure how I can account for this. Any feedback?

[caltang · Accepted solution]

For anyone who is keen on this, I did some experimentation and came to these options:

Because Boolean logic applies AND/OR - either WITH (default) or NOT (opposite), then the written expressions must be carefully laid out.

Scenario A: 

User wants to select the fields of their interest. 

Solution: Use Dynamic Select tool, then write the statement [Name] IN ('A') - Then, link it with a List Box interface tool that is itself linked by the File Browse interface tool, and the Action tool is configured to change 'A' only in the string. 

To do that, these links helps:

• https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/List-Box-amp-Filter-Tool/td-p/...
• https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Dynamic-selection-from-the-Lis... 

Scenario B:

User wants to write in their StartsWith, EndsWith, Contains - and their NOT counterpart statements.

This is where it gets tricky because NOT only works best when everything is selected (meaning to use main data directly), or if the user is aware that they are applying NOT on a subset of the main data.

So, I used two sub-options for B:

Sub-Option B1: Use the default StartsWith, EndsWith, and Contains statements in Dynamic Select first. Something like:

StartsWith([Name],'A1')
AND
EndsWith([Name],'A2')
AND
REGEX_Match([Name],".*(A3|A4|A5).*")

By doing so, I use three Text Box interface tools, all linked via their Action Tool that configures the strings for A1, A2, and A3|A4|A5 with the instructions laid out accordingly for REGEX_Match to have multiple contains with | as the delimiter. 

Now, if the end user does not want to use the StartsWith, EndsWith, or Contains (via Regex) as I picked up here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/RegEx-alternative-to-multiple-... - they can leave it blank. 

Now, for the NOT clauses, I added a 2nd Dynamic Select after the 1st Dynamic Select with the following expression:

NOT StartsWith([Name],'A1')
AND
NOT EndsWith([Name],'A2')
AND
NOT REGEX_Match([Name],".*(A3|A4|A5).*")

The NOT statement acts the same, and I add three more Text Box interface tools with their Action Tools configured exactly like before to update the A1, A2, and A3|A4|A5 portions. But the difference here is that for NOT statements, we have to put a string there as default text, otherwise it will let nothing pass through. Hence, I configured the default text in the Text Box tool to be "Default", because no column has the word Default in their start, end, or contains.

Now, for Sub-option B2, I do the same above - but with a new container and 2 new Dynamic Select tools. This time, NOT comes first, then WITH comes second - opposite from Sub-option B1 above - but with same configurations.

Will continue further on Scenario C in a new comment.

[caltang · Accepted solution]

Scenario C:

Continuing from before, I want to give end users ultimate and maximum flexibility to their heart's content. So, I make it an option for them to write their own expressions!

Using Dynamic Select, I wrote a simple "Hello" for them to replace with a Text Box interface tool and an Action tool configured to change the "Hello" string. In addition, I enabled the "Multi-line" option on Text Box interface tool to make it easier for end users to write their expressions.

Cleaning-up

Now, for all three scenarios, I place them each in their own tab via Interface Designer. 

I also placed their results with a Browse tool rather than an Output tool because the Browse tool also can export for the end users once it is enabled to activate in the Interface Designer, and end-users can click on the save button on the right of the Browse tool.

In addition, I added containers keep each scenario inside, and have an extra container to showcase the Browse results. Now, because of Boolean logic, all three may show results even when you don't select any option - and that is an issue. 

So, we add interface tools to the containers themselves! Add a Check-Box tool and a Condition Tool (configure to [1] only in the expression will do), and then the True output is connected to an Action tool, which is then connected to a Container tool. This enables you to turn on/off a container - in this case, turn on/off a selection! 

Now, for the browse results, you still need to add the same container logic above, but with a twist. Change the default value by ticking the option in the Select tool, and this means that the default selection will be to NOT show the results. So, end users will have to un-tick this box via analytic app AND tick their selection, to see their selection appear.

Hope this helps people down the road!