We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamically changing the formula based on the values of a column

Zaid
8 - 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
2 REPLIES 2
jdunkerley79
ACE Emeritus
ACE Emeritus

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
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
Labels