community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
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 1Number 2OperatorFlag
56<T
3434<F
3423>T
3434<=T

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

@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

Alteryx ACE & Top Community Contributor

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