3 Replies Latest reply on Dec 15, 2011 4:58 PM by Nathaniel Fitzgerald-Hood

    IN and NOT IN operators

    Jon Mitten

      Basically, I'd like to do this:


       IIF( [encode_type_id IN (98,99,100,101,102,103,104,105,106,107,108,109,136,140,146,184,235,257,295,296,338,339), 1, 0)

      Instead of this:


       IIF(((encode_type_id = 98) OR (encode_type_id = 99) OR (encode_type_id = 100) OR (encode_type_id = 101) OR (encode_type_id = 102) OR (encode_type_id = 103) OR (encode_type_id = 104) OR (encode_type_id = 105) OR (encode_type_id = 106) OR (encode_type_id = 107) OR (encode_type_id = 108) OR (encode_type_id = 109) OR (encode_type_id = 136) OR (encode_type_id = 140) OR (encode_type_id = 146) OR (encode_type_id = 184) OR (encode_type_id = 235) OR (encode_type_id = 257) OR (encode_type_id = 295) OR (encode_type_id = 296) OR (encode_type_id = 338) OR (encode_type_id = 339) AND status_code = 200), 1, 0)

        • 1. Re: IN and NOT IN operators
          Chris Gerrard

          Barring the introduction of IN and it's negation function (EXCLUDES? NOTIN?), this does the job cleaner than the repetitive recoding of "(encode_type_id =..." above:


          field: encode_type_match
           CASE [encode_type_id]
            when  3 then "y"
            when  70 then "y"
            when  97 then "y"
            when 129 then "y"
            when 166 then "y"
            when 325 then "y"
            else "n"

          Using an IN expression would reduce the amount of syntactic sugar in the expression, but the CASE statement isn't too bad, whipping it up from a list of values is straightforward in modern text editors.


          Also, the expressions you provided are not equivalent, the second has the

          AND status_code = 200

          clause, which the first lacks.

          Adding this clause is ambiguous; it's unclear what the intended scope of the AND is, and in any case Tableau chokes on the second expression with the error message: "Can't compare string and integer values".


          Assuming I've guessed your intention correctly, combining the tests into a single logical evaluation is straightforward, using the results of the first inclusion expression in a second calculated field's expression like so:


          field: type & status match
           if  ([encode_type_match] == "y")
          and  ([status_code]      == 200)
          then "true"
          else "false"


          I'm attaching a twbx using the sample Superstore Sales data with examples of the

          type & status match

          • 2. Re: IN and NOT IN operators
            Jon Mitten

            True- I left out the AND status_code = 200 by accident, and I cannot edit the OP. Thanks for this technique, though. It'll certainly help when I revisit these fields - much easier to look at.


            I think you've assumed my intention all right. I would then count the "true" values from

            type & status match
            confirmed downloads
            . Something like
            IIF([type & status match = 'true'], 1, 0)


            The further intention would be to have an exclusionary count, as well - an "All Others Field" or similar.

            • 3. Re: IN and NOT IN operators
              Nathaniel Fitzgerald-Hood

              Yes PLEASE! I was just lamenting the lack of this the other day. My solution for some groups was using COMTAINS, which does the trick:


              CONTAINS("TEES TESP TESC TESS TESD TECB TECC TECX TEUD TELDT APPR", [Work Group]) - the spaces makes them sort of discrete items in a list.


              What I would like is to be able to use the groups/set that one can setup - ideall I want to interrogate ApplicableWorkGroups (a group) and see if a given group is part of it, rather than only being able to filter and whatnot on them.


              - Nathaniel