4 Replies Latest reply on Aug 18, 2017 9:37 AM by Jen McElroy

    If/Then/Else statement

    Jen McElroy

      I have data that is like this and I'm trying to create a calculated field to count the number of cases that have particular event codes.

       

       

      Case NumberEvent Code
      1234562002
      1256082002
      1256083008


      I tried:

      If

          ([Event Cd] = '2002') THEN 1 ELSEIF

           ([Event Cd] = '3008') THEN 1 ELSEIF

           ([Event Cd] = '3003') THEN 1

      ELSE 0

      END

       

      I also tried this:

       

      If

       

          ([Event Cd] = '2002') OR

           ([Event Cd] = '3008') OR

           ([Event Cd] = '3003')

      THEN 1

      ELSE 0

      END

       

      The problem that I have is that if the case has more than one of the values it's returning 2 or 3 -- how do I get it to just return the number 1 regardless of how many event codes match these three codes specified?

        • 1. Re: If/Then/Else statement
          Jim Dehner

          Hi

          could you share your workbook in a twbx format or at least some screen shots that show the way the data is shown on the screen -

          In your if statement and in the case statement the function only processes until it hits the first True value - so if the first test is True (ie. event code = 2002 the function will return a 1 and stop -

           

          I did note that in your second and third record the case number is the same - in processing those records the if function will return a 1 on the second record (3008) and a true on the third record (2002) so when you put the case id 125608 in the viz with a sum() aggregation it will total 2

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: If/Then/Else statement
            Hari Chandan Reddy Maryada

            HI Jen,

             

            I think changing the calculated field to  Attribute instead of Sum() aggregation will fix your issue.

            2 of 3 people found this helpful
            • 3. Re: If/Then/Else statement
              Jen McElroy

              Hi Jim,

              Yes, as a case moves through the process it could, in fact, have multiple event codes that match. I don't care, in this instance if that is true though. I thought it would just drop out of the IF/THEN/ELSE statement after the first true was discovered but apparently not?2017-08-18 11_35_59-Tableau - Friday - LS Bureau Monthly Stats.png

               

              Also... I'm not sure how to upload my twbx....

              • 4. Re: If/Then/Else statement
                Jen McElroy

                When I changed it to ATTR it changed the value for the entire event code to 1 rather than counting each case separately.