Build a Formula

[Sa743liz-]

How can I build this formula in the workflow? 

[Emmanuel_G]

@Sa743liz- 

Find in attachement the way of doing this in Alteryx with dummies data. You can adapt in your workflow.

Let me know if it works and please, do not hesitate to mark this answer as solution if it helped.

[christina-billman]

Hello, I will walk us both through the solution as follows. A point I should make first, though, is that Excel uses formulas to reference specific cells, while Alteryx will refer to the column. So when I convert this into "Alteryx speak," you will have to go one step further and change the bracketed items into the column names.

Let's break this down a bit. Formatting your formula to help understand a bit better:

Looks like we have a giant "IF" statement with an "AND" statement and another "IF" statement embedded inside. 

1. Looking at the big encompassing IF statement, I can read that it's calculating IF [red box 1] is true, it will return YES. Otherwise, it goes into another IF statement which returns YES or NO
   1. Our Alteryx IF statement structure will be:
   2. IF [red box 1] THEN "Yes" ELSEIF [red box 2] THEN "Yes" ELSE "No" ENDIF
   3. The bold items will stay the same, but we need to figure out the logic for red box 1 and red box 2.
2. Let's break down red box 1. Looks like it's an AND statement with an OR statement embedded.
   1. Column AA7 has to equal "N/A", while also [Blue box 1] has to be true. 
   2. Our syntax for [red box 1] will be:
   3. [AA7]="N/A" AND [blue box 1]
3. Let's break down blue box 1. This looks like an OR statement with several pieces to it.
   1. AB7 has to be less than -1, or Z7 has to be any of Caa1, Caa3, or Ca. There's a nice Alteryx statement for the Z7 part we can use.
   2. Our statement for blue box 1 will be:
   3. [AB7]<-1 OR [Z7] IN ("Caa1", "Caa3", "Ca")
   4. Notice the "IN" function, this is an easy way to shorthand an OR statement in Alteryx.

So far, our formula is:

IF [AA7]="N/A" AND ( [AB7]<-1 OR [Z7] IN ("Caa1", "Caa3", "Ca") ) THEN "Yes" ELSEIF [red box 2] THEN "Yes" ELSE "No" ENDIF

Hopefully this is enough to go off of to complete the [red box 2].

Let me know if this was helpful!

Edit: Parenthesis