Formula Function to Evaluate String as Expression

[jasondika]

In Microsoft Access, using the combination of the "Eval" and "BuildCriteria" functions, I can take three fields--[A], [Operator], [B]--build a string with these fields, and evaluate that string as being true or false.  Is there a similar way to do this in Alteryx?  For instance, can I somehow use the Alteryx Formula tool to concatenate the fields into a string ([A] + [Operator] + [B]), then evaluate that string as a boolean expression (true/false)?

[A] and [B] may be text strings or numbers, so I would build a preceding step to add quotes around any strings.  [Operator] may be '=', '!=', '<', '>', 'Like', 'Not Like', etc.  Examples:

IF [A] < [B] THEN 'Y' ELSE 'N' END IF

IF CONTAINS(["A"],["B"]) THEN 'Y' ELSE 'N' END IF

[jdunkerley79]

You can do this using the CReW dynamic formula macro written by @AdamR_AYX:

http://help.chaosreignswithin.com/DynamicFormula.html

You can get the newest release https://community.alteryx.com/t5/Engine-Works-Blog/Crew-Macro-Pack-2016-Q2-Release/ba-p/26482

[jasondika]

Unfortunately, I'm still having trouble.  In my use case, each record has different operators.  When I connect both the "D" and "F" inputs to the output of my previous step, which is building the four Formula property fields (Output Field, Type, Size, and Expression) for each record, the Dynamic Formula tool in the CReW Macros library seems to only utilize the properties that are presented in the LAST "F" input record for every "D" input record.  Any thoughts?  Attached is a simple workflow example.

[jasondika]

Rather, I should say that the Output Field for EVERY record is True/-1 if the Expression for the last record is True (and vice versa for False Expressions).

[jasondika]

Still not resolved.  Attached is an updated example that better shows what I am trying to accomplish.  The left Dynamic Formula tool is what I am hoping to accomplish (also handling "Between", "Contains", "Begins With", etc., utlizing multiple operator fields).  Essentially, I cannot figure out a way for the Formula or Dynamic Formula tool to evaluate the [Operator] field (e.g., ">") as the condition for the expression.

[jdunkerley79 · Accepted solution]

Sorry think Dynamic formula might not help this time - its more for a single expression for all rows.

If you just have an operator and two data fields you could try a switch statement to handle the universe of operators you need to cover:

SWITCH(Operator, NULL(),
   '=',[Data1] = [Data2],
   '<',[Data1] < [Data2],
   '<=',[Data1] <= [Data2],
   '>',[Data1] > [Data2],
   '>=',[Data1] >= [Data2],   
   'contains',Contains(ToString([Data1]), ToString([Data2]))
)

Attached a sample - happy to help build out further

[jasondika]

Great, thanks!  Attached is a comprehensive example for all of the conditions I could think of.

[HimanshuM]

R has a function called eval() which reads the string as expression and replaces the output in-place. A combination of the script in the Run Command Tool could do the trick.