3 Replies Latest reply on Oct 10, 2015 10:18 PM by Matt Coles

    SQL 'NOT EXISTS' IN TABLEAU

    Johh Bar

      I would like to know how to apply the SQL Function “NOT EXISTS” in Tableau.

       

      Context

       

      One of the business rules is to calculate a count of PersonIDs that Where Code = X1 OR Code = X2 AND NOT Code = Y1 OR Code =Y2.

       

      Relationship: ONE PersonID can have MANY Codes

      Field: PersonID

      PersonID Values: 1001, 1002, 1003, 1004… etc.

      Field: Code

      X1, X2, X3, Y1, Y2, Y3, Z1, Z2, Z3,

       

      The way to do that in SQL is:

      SELECT

      elg.PERSON_ID,

      'X' AS CODE

      FROM elg

      WHERE elg.CODE IN ('X1', 'X2')

      AND NOT EXISTS ( -- Not Y

      SELECT 'X'

      FROM elg elg2

      WHERE elg.PERSON_ID = elg2.PERSON_ID

      AND elg2.CODE IN ('Y1', 'Y2'))

       

       

      How can we apply this business rule in Tableau? After doing some research, I found http://community.tableau.com/thread/172103, but since the Dimension values are text, you can’t apply aggregate conditions.

        • 1. Re: SQL 'NOT EXISTS' IN TABLEAU
          Bill Lyons

          Tableau's default data Data Source editor does not provide for subqueries at all, regardless of whether "not exists" is connected to it). The only way to do that directly is to use custom SQL, which is available below the list of tables.

           

          Depending on the application, when I have a non-standard query, requiring subqueries, unions, cross-database joins, or other special SQL code, I occassionally use custom SQL. More frequently, I create a view in SQL, and connect to that view in Tableau.

          • 2. Re: SQL 'NOT EXISTS' IN TABLEAU
            Matt Coles

            Custom SQL can work in this situation, but make sure you are using the minimum amount of your connection logic in the portion that's Custom SQL (in previous versions, your connection had to be "all Custom SQL" or "no custom SQL"). Write your subquery as Custom SQL, then join it to your other tables. It may seem weird, but it makes your connection easier for others to understand, and it'll be far more efficient than making the connection entirely Custom SQL, too.

             

            Another approach might be LOD calcs. You could write a calc that returns 1 or "true" if the record is it's Y1 or Y2, 0 if it isn't. Then use something like { FIXED PersonId : MAX([In a Y Group]) }. That calculation is effectively saying "For each record, provide a 1 if that PersonId is in a Y Group, 0 if they aren't".

             

            Without seeing your workbook it's hard to know how to advise the best way on this...if you have a mockup of the data, and what you're trying to do, it might make our proposals a bit more concrete. Hope that helps anyway though!

            • 3. Re: SQL 'NOT EXISTS' IN TABLEAU
              Yuriy Fal

              Hi John,

               

              With the dataset as you've described,

              one could do a couple of conditional aggregate filters

              and apply them to the PersonID and a copy of the PersonID fields.

               

              1st Conditional Filter calc (place PersonID on a Filter shelf, choose Select All, then Condition -> by Formula):

              max(

              sum(if [Code] = 'X1' then 1 end),

              sum(if [Code] = 'X2' then 1 end)

              ) > 0

               

              2nd Conditional Filter calc (place PersonID (copy) on a Filter shelf, choose Select All, then Condition -> by Formula):

              max(

              sum(if [Code] = 'Y1' then 1 else 0 end),

              sum(if [Code] = 'Y2' then 1 else 0 end)

              ) = 0

               

              One could subsequently create a dynamic Set with this filter combination.

               

              Please find the attached wb as an example.

              Hope this could help.

               

              Yours,

              Yuri