cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Dynamically changing the formula based on the values of a column

Highlighted
Asteroid

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 Nebula

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. Alteryx Certified Partner

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels