Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

multiple condition IF formula

EtamSoko
8 - Asteroid

Hi Community! I decided to ask for help after trying many variations for my formula with multiple conditions without any success. It does not give me error message, but when I validate the results, it just not correct. I will write in english what I want to achieve.

 

1. if order status contains 'work complete' or 'invoiced' return value "0"

2. if Main work center contains 'CANC' or 'Schedule' then return "0". But in any other case (only true for the 2nd condition), additionally if the System status contains "Closed" only then return "1"

 

Every other case (including condition 1) return value "1"

 

if (!Contains([Order Status],"Work Complete","Invoiced") && Contains([Main work center], "CANC","SCHEDULE")) THEN "0" ELSE "1" ENDIF - I got until this point but it already didnt work...without including the System status condition.

 

I hope it is understandable , and someone can help me resolve this.

I really appreciate the support in advance!

regards,

MK

8 REPLIES 8
wdavis
Alteryx
Alteryx

Hi @EtamSoko 

 

For your Contains formulas, you will need to separate these out for each condition e.g.

 

if (!Contains([Order Status],"Work Complete") AND !Contains([Order Status], "Invoiced")

 

Let me know if this fixes it for you. Otherwise, if you want to provide a small sample of the table we can help build this for you

 

Thanks

Will

kelsey_kincaid
12 - Quasar

Hi @EtamSoko ,

Here's a rough example of what I think you're looking for. I assumed in the below formula that if [System Status]='Closed' then it wouldn't matter what other conditions were fulfilled, you would just want it to be 1. If that's not the case then let me know and I can help you adjust.

 

IF [System Status]="Closed" THEN 1 ELSEIF

Contains([Order Status],"Work Complete") OR Contains([Order Status],"Invoiced") THEN 0

ELSEIF Contains([Main Work Center],"CANC") OR Contains([Main Work Center],"Schedule") THEN 0

ELSE 1 ENDIF

 

kayers_0-1578583550770.png

EtamSoko
8 - Asteroid

hi Kayers,

 

this looks I believe really good but I have a few problems with it when I run. 

 

First of all let's disregard the System status condition entirely.

 

Regarding the rest of the formula, when I put into the same formula box, either to every row returns 0 value, or some rows just simply incorrect - after validating them 1 by 1.

 

So I figured I am going to put into 2 separate box by splitting the conditions based on the field that it the formula is looking at (order status,main work center) but it also doesn't work properly because 1 overwrites the other.

 

I am a bit confused what should I change in the formula. I might add more to the query that I missed to mention before. First of all the formula should look at the Order status field if there is Work complete or Invoiced. If none of the above is true then look at the Main Work center column and return 0 or 1 based on the formula. The reason for this is that Order status column might be Null sometimes, so then I need the formula to look at the Main Work Center column.

 

Hope this is not too complicated. let me know please if you need anything else in order to be able to resolve it.

 

Thanks a lot!!

MateK

RolandSchubert
16 - Nebula
16 - Nebula

Hi @EtamSoko ,

 

I think, the main task is to sort out the priority of the conditions. My understanding is:

 

Condition 1: [Order Status] = 'Invoiced' or [Order Status] = 'Work complete'

Condition 2: [Main Work Center] = 'CANC' or [Main Work Center] = 'Schedule'

Condition 3: [System Status] = 'Closed'

 

If condition 1 is met, then result is 0, independent of other conditions

In condition 1 is not met, but  condition 2 is met, result is also 0 (again independent of other conditions)

If neither condition 1 nor condition 2 is met and condition 3 is met, then 1

In any other case: 1 (condition 3 makes no sense in my opinion, as it has no impact)

 

Sample input data could be like this:

10-01-_2020_07-50-55.png

 

and formula would be:

10-01-_2020_07-51-19.png

Hope this is helpful in any way, at least to "organize" the conditions.

 

Best,

 

Roland

EtamSoko
8 - Asteroid

Thank very much Roland. Also, thanks for all of you trying to help me.

 

I needed this re-organization also in my main formula but it works now perfectly!

 

General experience with Alteryx that many user lacks from basic knowledge. Personally,I could not find good training materials about basic principles,how Alteryx differs from excel for ex. when it comes to i.e. formulas,e tc.. only explanations about how every tool work. In case you would recommend material that I can go through , it would be highly appreciated!

 

Have a great weekend.

 

Thanks a lot,

MateK

 

 

RolandSchubert
16 - Nebula
16 - Nebula

Did you already have a look at: https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Excel%20Us...?

It's a part of the Interactive Lessons at Alteryx Academy and explains fundamentals of Alteryx for Excel users. 

In addition, https://community.alteryx.com/t5/Videos/bd-p/live-training contains a session specifically designated for Excel users.

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

Now that this is solved, let me offer everyone some simple advice. 

keep it simple!

 

 Don't be ashamed of using filters!  They actually help to run the workflow faster, make it easier to debug and make it easier to maintain. 

You can filter the conditions a, not a in one filter and b, c (else) in another filter. 

now you can write simple IF,..Then....else statements. 

use a union to bring the data back together. 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
EtamSoko
8 - Asteroid

Really appreciate the mind opening advices from both of you.

 

br,

MateK

Labels