multiple condition IF formula

[EtamSoko]

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

[wdavis]

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]

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

[EtamSoko]

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 · Accepted solution]

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:

and formula would be:

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

Best,

Roland

[EtamSoko]

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]

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]

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

[EtamSoko]

Really appreciate the mind opening advices from both of you.

br,

MateK