1 Reply Latest reply on Jul 15, 2018 7:37 PM by swaroop.gantela

    Compare a filtered list against a fixed (or master) list and return NON-Matches

    Jon Vance

      Hi Tableau colleagues,


      I am sure this is easy, but I can’t figure it out.  I need to compare Vendor filtered sites against a master list of 915 sites.


      My data has internet sites and vendors.  There are a total of 915 sites.


      The data shows the 915 individual sites by Site ID (Sheet Internet Site Count).

      The data shows the 17 Vendors (there is no Vendor 5) (Sheet Vendor List).

      The vendors bid on a different quantity of sites.  For example, Vendor 1, 7, 12,13, and 18 bid on all 915 sites.  But others bid on fewer sites, as low as only 31 sites for Vendor 6 (Sheet Total Sites Bid On).

      Finally, I have data on what sites the Vendor bid on (Sheet Site List by Vendor).


      What I am trying to return is the Site ID the vendor DID NOT bid on, by returning the population of 915 sites NOT INCLUDING the sites they did bid on.


      For example, on Sheet Site List by Vendor, when you filter on Vendor 6, there are only 31 sites he bid on.
      How do I return the list of 884 sites he DID NOT bid on?  Another example, Vendor 9 bid on 914 of the 915 sites.  Which ONE did he not bid on?


      Any assistance would be greatly appreciated.  Attached is .twbx.





      Version 10.5.1 (10500.18.0210.2209) 64-bit

        • 1. Re: Compare a filtered list against a fixed (or master) list and return NON-Matches



          I'm not sure if this setup will work for your true data set,

          but maybe it can give an idea.


          This method is dependent upon the ability to cross-join

          your data to a simple list of all sites using a calculated field of 1.


          The LookupList will have all sites, so you can fix to it whether

          or not a Vendor Bid that site using:

          { FIXED [Vendor1],[Lookup Site ID]:MIN(

          IF [Site ID1]=[Lookup Site ID] THEN "Did Bid"

          ELSE "Did not bid"



          Also, this method may be prohibitively intractable if the

          size of your true dataset is large. But there may be ways

          to speed it up.


          Please see workbook v10.5 attached in the Forum Thread.