1 Reply Latest reply on Feb 25, 2016 12:10 PM by Joe Oppelt

    Calculated filed - Contain with IF function or Contain with CaseWhen Function?

    Flora Fu

      Hi all,

       

      I am trying to write a calculated field with CONTAIN function, and I also have IF/ ELSEIF/END  within it. But I find out the results did not give what I was looking for.   With my formula below, the logical  produces first " IF" then "Else If",etc, appears to be no overlap -  if it has "Adjust", it will only calculated as adjust although it may also contains " Correct" and "Error" , but what I expect is to count without the IF hierarchies - in one item contians all keywords, it will be counted three time accordingly.   ..  Should I use Case when instead?  Please advice 

       

       

      IF  CONTAINS([Header Description],"adjust") OR  CONTAINS( [Line Description],"adjust") OR CONTAINS( [Header Name],"adjust")

      then'Adjust'

       

      ELSEIF  CONTAINS([Header Description],"error") OR  CONTAINS( [Line Description],"error") OR CONTAINS( [Header Name],"error")

      then'error'

       

      ELSEIF  CONTAINS([Header Description],"correct") OR  CONTAINS( [Line Description],"correct") OR CONTAINS( [Header Name],"correct")

      then'correct'

       

      else "Null"

      end

       

      Any help is very much appreciated !

       

      Thank you

       

      Flora

        • 1. Re: Calculated filed - Contain with IF function or Contain with CaseWhen Function?
          Joe Oppelt

          Whether you use CASE or IF, as soon as a TRUE condition is met, the calc stops evaluating.

           

          If you want to see if all three conditions are met, make 3 calcs.  Maybe make each one result in a 1 or 0.  Then add the three calcs together in another calc to get the total number of conditions met in each row (if you need to know that).   And the SUM([Error calc]) will give you how many rows contained ERROR, and SUM([Adjust calc]) will tell you how many rows have ADJUST, etc.