Dynamically changing the formula based on the values of a column

[Zaid]

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

[jdunkerley79 · Accepted 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.

[MarqueeCrew · Accepted solution]

@Zaid,

@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