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

    Converting Multiple If Statements into one Calculated Field

    Paul Ozule

      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
          Andrew Watson

          What about:

           

          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
            Paul Ozule

            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