User values from multiple fields to create a formula that will filter another data set

[aehrenwo]

Is there an easy way to read a row of values and create dynamic filter for a data set based off that input... so filter these 4 different fields with one formula input? 

Regards,

Adam

[JohnJPS · Accepted solution]

Hi @aehrenwo,

I think you could define the variables in a row of values, as you mention, then just append fields to add that to your data set; then use those variables directly in a formula. (see attached workflow for an example).

Hope that helps!

John

[SeanAdams]

Hey @aehrenwo

As @JohnJPS says you could use a formula - and the other thing you could do is add an interface component which requests the filter value from you at run-time.

The quickest way to get you to a solution that exactly meets your needs is if you could mock up (similar to the way that @JoeM does the weekly challenges) an example workflow of what you want from the Input, and from the output, and attach the workflow to this thread in your next reply.   I appreciate that you cannot send company proprietary info, hence the need to mock up an example.   This way the community can band together on your specific need and crack through an answer really quickly.

BTW - if you're on Alteryx 11 you can use the universal search for the Text Box - and it will link you through to a few help articles on this.

Thanks @aehrenwo

Sean

[aehrenwo]

John - this is very helpful. thank you. 

The only thing I am running into now is if say only one of the filter values that are being appended is filled in... In your example if only f4 has a value but the other 3 are null, is there a way to filter based on only the one criteria versus all 4? 

Would the Dynamic select tool be needed?  

I am guessing the formula could just be adjusted to result in a new field that puts a Y/N or 1 or 0 if the field is null and if there is a match put Y or 1 and then filter on that? 

Thanks 

Adam

[aehrenwo]

Nevermind ... i think using OR statements solves my problem. If any of the items match then it gets included .... 

If I run into any other issues I will let you know

[JohnJPS]

Sounds like you have it; great!  Just FYI yes, you could use conditional logic in your filter expression too; e.g. 

IF NOT IsEmpty([f1]) THEN
    ... checks against [f1] ...
ENDIF
AND/OR
(etc)

[aehrenwo]

One additional follow... 

If the "criteria" file in question has more than one row how can I expand to workflow to process each row separately and then join the results together? 

I am reviewing Iterative macros now but it is not clear how to handle that. I have successfully set up macros that process entire files in a folder but have never done something that reviews all lines in one file. 

Thanks,

Adam

[aehrenwo]

One other question - If the custom filter expression needs to be ignored if NONE of the criteria is met what should be put the ELSE section?  I was not able to find any explanation in the Help . 

Adam

[JohnJPS · Accepted solution]

Hi Adam,

I'll have to think about the first question a bit; (i.e. might have to ponder off hours)

For ignoring a filter, this is basically the same as setting it to True... so something like....

IF (... all sorts of stuff ...)
     .... all sorts of decisions ...
ELSE
     1
ENDIF

Should hopefully work...  basically the ELSE 1 ENDIF is what you're after, I think.

Hope that helps!

[aehrenwo]

Thanks....

It will be really helpful. I understand the batch concept for running through multiple files but not row by row for same task. 

Regards,

Adam