3 Replies Latest reply on Jun 6, 2018 11:43 PM by SeaUrchin

    Help in building case when

    Grishma Achrekar

      Hello All,

       

      I want to build a "case when" statement that looks something like this

       

      Case contains(StateTag , <<Value>>) = TRUE

      when <<Value>> = "1" then 'maharastra'

      when <<Value>> = "2" then 'delhi'

      when <<Value>> = "2,3,4" then "NCR"

      end

       

      How do i go about such a statement ?

       

      I cannot use "IF ELSE" because in case of NCR it wont work. if it finds 2 it will name it Delhi and move to ELSE .

      But i want it to check all StateTags and name it whereever there is 2.

       

      Any other way than case when is also welcome.

       

      Thanks

        • 1. Re: Help in building case when
          Michael Hesser

          Grishma--

           

          Can you use IF/ELSE but reverse your order so the largest strings are first (NCR, then dehli, then maharastra) ?

           

          IF CONTAINS([StateTag],"2,3,4") THEN "NCR"                    // Look for the largest string first

          ELSEIF CONTAINS([StateTag],"2") THEN "dehli"                 // Look for smaller strings

          ELSEIF CONTAINS([StateTag],"1") THEN "maharastra"      // Look for smaller strings

          ELSE "not in checklist"                                                          // Error if nothing is found

          END

          • 3. Re: Help in building case when
            SeaUrchin

            Hi Grishma,

            It looks like your code will always be 'delhi" when there is a "2" value:

            when <<Value>> = "2" then 'delhi'

            when <<Value>> = "2,3,4" then "NCR"

             

            To make it evaluate whenever there is a 2, then it should be nested:

            --this is a pseudo code (ie. not real code) but it's to guide you to place your code in the right order

            Case contains(StateTag , <<Value>>) = TRUE

            when <<Value>> = "1" then 'maharastra'

            when <<Value>> contains "2" then  //instead of just putting 'delhi' here, set up another if-else to refine further:  --at this point , all values with a "2" will excecute the code below

                           if <Value does not have 3, 4> then 'delhi'

                           else "NCR"

                           end

            end

             

            Regards

            SeaU