Hi All,
I am trying to create a workflow based on a logic and I am stuck. There are three columns, Number 1, Number 2 and Operator. The Number 1 and Number 2 contains values which are numeric and the Operator contains operators(<,>,<=,>=). Based on the value of the operator in each row, I want to create a flag(new column) which will tell me if the operator for that row is placed correctly.
Example is listed below:
Number 1 | Number 2 | Operator | Flag |
5 | 6 | < | T |
34 | 34 | < | F |
34 | 23 | > | T |
34 | 34 | <= | T |
Solved! Go to Solution.
Given the limited list of operators I would suggest a formula of;
Switch([Operator], NULL(), "<", [Number 1] < [Number 2], ">", [Number 1] > [Number 2], "<=", [Number 1] <= [Number 2], ">=", [Number 1] >= [Number 2] )
If you do want to do a dynamic formula then the CReW macro pack maintained by @MarqueeCrew as a macro to do this. You need to build the expression and then it will build the column for you.
@jdunkerley79 is right, but his formula already takes enough effort without the addition of a new tool. At Inspire in 2019 I may be training on CReW macros and would include a Dynamic Formula lesson. Here I will provide you with a different STATIC formula that gets you what you need.
IF [Number 1] < [Number 2] AND [Operator] = "<" OR [Number 1] > [Number 2] AND [Operator] = ">" OR [Number 1] = [Number 2] AND [Operator] = "=" OR [Number 1] <= [Number 2] AND [Operator] = "<=" OR [Number 1] >= [Number 2] AND [Operator] = ">=" THEN 'T' ELSE 'F' ENDIF
James provided you a solution that assumes that your flag is boolean ('True' and 'False'). If you output to a string, you get -1 for True and 0 for False. His formula also defaults to Null if the operator isn't one of the listed values (that is an improvement/feature over my expression).
The benefit of my expression is that it is easy to read and it gets you to the "T" or "F" output that you requested. In a subsequent formula, you can update the flag as:
IF Operator IN ('<', '>', '<=', '>=', '=') THEN [FLAG] ELSE Null() ENDIF
While typing that second expression I realized that I had included an = operator. That may be an added bonus.
Cheers,
Mark