2 Replies Latest reply on Jan 29, 2016 9:08 AM by Paul Ozule

# Converting Multiple If Statements into one Calculated Field

Hello,

I have a set of conditions i need to combine into one calculation. Now i could create each individual calculation and sum the results, but that sounds inefficient. Here is a sample of the condition and my attempt.

If([Department Code] = "OTHER" And [Order Method Code] <> "WEB";"01-Other";

If([Department Code] = "INSURANCE"; "01-Other";

If([Department Code] = "UNK" And [Order Method Code] = "NONE"; "01-Other";

If([Department Code] = "UNK" And [Order Method Code] = "M"; "01-Other";

If([Department Code] = "UNK" And IsNull([Order Method Code]) ; "01-Other";

If([Department Code] = "UNK" And [Order Method Code] = "UNK" ; "01-Other";

If([Department Code] = "UNK" And [Order Method Code] = ""; "01-Other";

I want to create an "Other Count".

IF [Market Code Department Code] = 'UNK' and

[Order Method Code] = 'UNK' THEN ([Count]) END

The code above is valid and i tried adding ( plus) the query above to the one below, but that doesn't work.

IF [Market Code Department Code] = 'OTHER' and

[Order Method Code] <> 'WEB' THEN [Count] END

• ###### 1. Re: Converting Multiple If Statements into one Calculated Field

IF([Department Code] = "UNK" and ([Order Method Code] = "None" or [Order Method Code] = "M" or [Order Method Code] = "UNK" or [Order Method Code] = "" or [Order Method Code] is null) THEN "01-Other"

ELSEIF [Department Code] = "Other" and [Order Method Code] != "WEB" THEN "01-Other"

ELSEIF [Department Code] = "Insurance" THEN "01-Other"

END

You can do a COUNT on that field or alternatively wrap the entire IF statement in the COUNT() - i.e.

COUNT(

IF([Department Code] = "UNK" and ([Order Method Code] = "None" or [Order Method Code] = "M" or [Order Method Code] = "UNK" or [Order Method Code] = "" or [Order Method Code] is null) THEN "01-Other"

ELSEIF [Department Code] = "Other" and [Order Method Code] != "WEB" THEN "01-Other"

ELSEIF [Department Code] = "Insurance" THEN "01-Other"

END)

• ###### 2. Re: Converting Multiple If Statements into one Calculated Field

Thank you Andrew. I used the first recommendation and it worked. posting the calculation for future reference.

IF [Market Code Department Code] = "UNK" and ([Order Method Code] = "None" or [Order Method Code] = "M"

or [Order Method Code] = "UNK" or [Order Method Code] = "" or ISNULL([Order Method Code])) THEN [Count]

ELSEIF [Market Code Department Code] = "Other" and [Order Method Code] != "WEB" THEN [Count]

ELSEIF [Market Code Department Code] = "Insurance" THEN [Count]

END