Forum Discussion

evalromf's avatar
evalromf
Helper I
9 years ago

AND IF in conditional column

Hi,   I'm trying to create a new column that should be populated with a certain number based on the contents of several other columns.   What I'm looking for is something like this:   If [COUNT...
Need Help
GilbertQ's avatar
GilbertQ
Super User
9 years ago

Hi there, the following will work, when in the Query Editor, click on Add Column in the ribbon and then Custom Column

 

if [Course Name] = "Managing Information" or "Promoting Positive Workplace Behaviour" 
and [Organisation / Portfolio] = "University House" or "CAVAL" then "YES"
else "No"

NOTE: The syntax for the "if" "or" "and" "then" "else" is all case sensitive.

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

You need to add some paranthesis as and has higher priority than or.

In your case the formula should be built like (a=x or a=y) and (b = m or b = n) etcetera.

Without paranthesis, a=x or a=y and n=m or b=n is equivalent with a=x or (a=y and b=m) or b=n.

So without parenthesis, you can think of all (groups of) ands evaluated first and the ors being in between (groups of) ands. 

 

  • rayinOz's avatar
    rayinOz
    Helper III
    9 years ago

    Thanks MARCEL! I'll give it a shot in a moment!

    • rayinOz's avatar
      rayinOz
      Helper III
      9 years ago

      So, this is what I have and I get an "error" in the new custom column...

       

      if ([Course Name] = "Managing Information" or "Promoting Positive Workplace Behaviour") 
      and ([#"Organisation / Portfolio"] = "Bio21 Australia Ltd" or "Bio21 Australia Limited" 
      or "CAVAL" or "Grattan Institute" or "Kendall Hall" or "Melbourne University Publishing Ltd" 
      or "Nossal Institute Ltd" or "University House" or "Australian National Academy of Music Ltd" 
      or "Melbourne Theatre Company" or "MU Student Union Ltd") then "Exclude" else "Include"

       

      This the error:

       

      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago

        Have you tried putting the follwing below?

         

        if ([Course Name] = "Managing Information" or [Course Name = "Promoting Positive Workplace Behaviour")

        And then repeat for each instance?