8 Replies Latest reply on Feb 11, 2014 11:11 AM by Joshua Milligan

    Using IN or INLIST in variable

    Kashif Saeed
    Kashif Saeed Feb 3, 2014 7:39 AM

      I am creating a variable in Tableau and am trying to create an IN or INLIST which says something like:

       

      IF x IN ('A', 'B') THEN 'Yes' ELSE 'No' END

       

      I am not getting this to work and it seems like IN or INLIST does not appear to be available.

       

      Is there a different keyword for this that I should use?

       

      Thanks,

      Kashif

        • 1. Re: Using IN or INLIST in variable
          Dylan Snyder
          Dylan Snyder Feb 3, 2014 8:00 AM (in response to Kashif Saeed)

          See Joshua Milligan's answer here: http://community.tableau.com/message/208986.

           

          If you have a long list of comparisons, an alternative would be to use your IN operator in your database query to create a separate column before importing or connecting to your data.

          • 2. Re: Using IN or INLIST in variable
            Matt Lutton
            Matt Lutton Employee Feb 3, 2014 7:52 AM (in response to Kashif Saeed)

            Can you describe what you are trying to accomplish?  I am not familiar with these functions so I'm not sure what you are hoping to do in Tableau.  These functions do not exist, that much I can tell you for sure.

             

            There is a CONTAINS function--so, if you were trying to find out if a field contains specific text, you can write calcs like:

             

            IF CONTAINS([FieldName], "xyz") then "XYZ product" else "ABC product" end

             

            Hope this helps--if not, please clarify what you are trying to accomplish.  Cheers!

            • 3. Re: Using IN or INLIST in variable
              Kashif Saeed
              Kashif Saeed Feb 3, 2014 7:59 AM (in response to Dylan Snyder)

              Thanks for your responses.

              @Dylan - I know it is an option, but I am not using Custom SQL. If I were using Custom SQL then I'd have created the CASE WHEN statement in the SQL itself. I'll be surprised if such a basic thing is not possible using calculations.

               

              @Matthew - I am creating an IF ESE condition, but need to compare multiple values instead of one. Eqal To (=) operator only works if you are comparing one value and one has to use IN or INLIST if comparing multiple values.

              • 4. Re: Using IN or INLIST in variable
                Dylan Snyder
                Dylan Snyder Feb 3, 2014 7:57 AM (in response to Kashif Saeed)

                FYI, case statements are supported in Tableau.

                • 5. Re: Using IN or INLIST in variable
                  Kashif Saeed
                  Kashif Saeed Feb 3, 2014 8:02 AM (in response to Dylan Snyder)

                  I understand that CASE statement is an option, but as I mentioned, I am not using a custom SQL.

                  I am creating a Calculation field in Tableau and need to use IN or INLIST as the operator instead of =

                  • 6. Re: Using IN or INLIST in variable
                    Dylan Snyder
                    Dylan Snyder Feb 3, 2014 8:05 AM (in response to Kashif Saeed)

                    Right. I meant that you can use them in your workbook in a calculated field.

                     

                    Logical Functions

                    • 7. Re: Using IN or INLIST in variable
                      Kashif Saeed
                      Kashif Saeed Feb 3, 2014 8:27 AM (in response to Dylan Snyder)

                      Thanks for your continued help Dylan!

                       

                      I tried the CASE statement in the calculated field. I have two Examples below:

                       

                      CASE [MARKET_GROUP_CD] WHEN ("RIA") THEN "YES" ELSE "NO" END

                       

                      CASE [MARKET_GROUP_CD] WHEN ("RIA","RINT") THEN "YES" ELSE "NO" END

                       

                      The first one works, but the second one does not. It is the exact same problem as with IF that it is expecting One value.
                      In other words, the built in Operator for the CASE statement is also =

                       

                      It is the second one that I am having problems with.

                       

                      One other option that I can use is to use OR, but it will be extremely painful to write OR statements for 5 things in the List.

                      • 8. Re: Using IN or INLIST in variable
                        Joshua Milligan
                        Joshua Milligan Feb 11, 2014 11:11 AM (in response to Kashif Saeed)

                        Kashif,

                         

                        Have you tried sets?

                         

                        If you right click your field [MARKET_GROUP_CD] in the data window on the left and select "Create Set", then you should be able to check the codes that make up your set.

                         

                        Then you can use the newly created Set in your view to show or evaluate whether records are IN/OUT of the set.  (This functionality is not available with Excel, text files, or Access as they use the MS JET driver that does not support advanced set functions [you can extract] and also OLAP/cubes do not support the IN/OUT function.  Any other data sources should be fine)  With any data source, you can use the set as a filter to keep only members of the set in your view.

                         

                        Regards,

                        Joshua