Multiple if condition

[ashriva2]

Hi, I require help with Below:

I have 3 columns named as "Prep 1", "Prep 2", "Prep 3"  I need to create new column as "prep4" having below conditions:

If "Prep2" is Null then take value from "Prep3"

If "Prep3" is Null then take value from "Prep2"

If "Prep2" and "Prep3" both are NOT Null then take value from "Prep2"

If "Prep2" and "Prep3" both are Null then take value from "Prep1"

[PhilipMannering · Accepted solution]

Hi @ashriva2 

This should do the job,

if isnull([Prep2]) and isnull([Prep3]) then [Prep1]
elseif isnull([Prep3]) then [Prep2]
elseif isnull([Prep2]) then [Prep3]
else [Prep2] endif

[PhilipMannering]

Actually, if you don't have any zeros, I think you get the same thing with the simplified,

if [Prep2] then [Prep2]
elseif [Prep3] then [Prep3]
else [Prep1] endif

[ashriva2]

So I have tried but in case if both Prep 2 and Prep 3 are having value it is taking value from Prep2 however I need it from Prep3

[PhilipMannering]

Not according to your original post. However you can switch the second and third clause in that case,

if isnull([Prep2]) and isnull([Prep3]) then [Prep1]
elseif isnull([Prep2]) then [Prep3]
elseif isnull([Prep3]) then [Prep2]
else [Prep2] endif