5 Replies Latest reply on Jan 15, 2019 2:14 PM by Divya Lingwal

    Using NOT IN?

    Bill Bosworth

      Hello - I'm trying to get to a view in Tableau without having to build it in SQL first.  The requirement is to measure new FirstNames where their Flag value = 1 AND they do not have any other previous entries in the table (where their Flag = 0).    I can do this in SQL with the following query, but cannot figure out how to do this in Tableau.  I've attached a workbook with a sample of data, appreciate any ideas.

       

      SELECT DISTINCT FirstName

      FROM TABLE

      WHERE EndDate BETWEEN '2014-12-03 00:00:00' AND '2014-12-04 00:00:00'

      AND FLAG = 1

      AND FirstName NOT IN

        (  SELECT DISTINCT FirstName

        FROM TABLE

        WHERE startDate BETWEEN '2014-12-03 00:00:00' AND '2014-12-04 00:00:00'

        AND Flag = 0

        )

        • 1. Re: Using NOT IN?
          Shine Pulikathara

          Hi Bill,

           

          One natural approach I could think of to solve this is to use sets. See Creating a Set if you are not familiar with Sets.

           

          1. Right click on First Name in the Dimensions pane and click Create Set.

          2. Create "Flag 1 Set": Set Computation by Formula: MAX([Flag])=1

          3. Create a similar set "Flag 0 Set": Set Computation by Formula: MIN([Flag])=0

          4. Select both Sets (by holding down the Control Key) and right click to create a combined set. Select the option to include all members from Flag 1 Set but exclude members from the Flag 0 Set. Call this set say "Flag 1 No Flag 0 Set".

          CombinedSet.png

           

          5. You can place this new set on the color shelf or filter shelf depending on how you want to view the results.

           

          I have attached a sample workbook with the approach I used. Let me know if this is what you were looking for.

          • 2. Re: Using NOT IN?
            Bora Beran

            If you're using 9.0 you can do {fixed FirstName : COUNT([Flag])} instead of Count(Flag) this will give you count of flag per First Name. Then you can drag this field onto filter shelf and filter it down to names that have a count of 1. Then you can drag the Flag field to filter shelf, select all value and filter out all rows that have a flag of 0. That should do it.

            • 3. Re: Using NOT IN?
              Alex Blakemore

              If what you want is to identify the FirstNames that don't have any records with Flag = 0 and have at least one record with Flag = 1, then you make a set based on FirstName with the condition "min(Flag)=1" (use the condition tab, and check the summary at the bottom of the General tab)

              Name_Set.tiff

               

              If your condition depends on the time sequence order of the records, then you'll probably have to use a table calculation or custom SQL.

               

              That is, this set doesn't care about the End_Date field. It just looks at the group of records with the same FirstName and checks the Flag field. If your calculation depends on whether the 1s come before or after the 0s when ordered by some other field, things get more complex. Even if you do enforce some rule about ordering, life will be easier if most of your calculations are defined in way that they can ignore that detail, or if they can just assume that the rule was already enforced.

              • 4. Re: Using NOT IN?
                Bill Bosworth

                I think this will do the trick - thank you Shine, Bora and Alex!

                • 5. Re: Using NOT IN?
                  Divya Lingwal

                  In formula for First Name, we can also use : If MAX([Flag]) =1 and MIN([Flag])=1 , This will give us only the ones with Flag=1 and exclude the common ones.