2 Replies Latest reply on Mar 1, 2013 1:11 AM by Xavier P

    Checking presence across multiple list

    Xavier P

      Hello Tab-heads and Zen masters!

       

      I have 3 lists, A,B and C - attached.

       

      I need to know who is on list A only, A+B, A+C and B+C

       

      I ran this calculation with booleans but it does not seem to be yielding the expected results:

       

      IF [A2B] AND ISNULL([B List (Checklist.xlsx)].[B2C]) THEN "Also on B"

      ELSEIF [A2C] AND ISNULL([B List (Checklist.xlsx)].[B2C]) THEN "Also on C"

      ELSEIF ([B List (Checklist.xlsx)].[B2C]) THEN "On B+C"

      ELSE 'A list only'

      END

       

      Anyone can see why?

       

      Thanks in advance

      Xavier

        • 1. Re: Checking presence across multiple list
          Tracy Rodgers

          Hi Xavier,

           

          C list needs to be the primary source since it has the most records. Then, the linking fields need to be on the view as well (place email B and email A on the level of detail shelf).

           

          Then, try a calculation similar to the following:

           

          if isnull(ATTR([B List (Checklist.xlsx)].[email B])) and max([email C])=max([email C]) and isnull(ATTR([A list (Checklist.xlsx)].[email A])) then 'C only'

          elseif  ATTR([B List (Checklist.xlsx)].[email B])=ATTR([B List (Checklist.xlsx)].[email B]) and ATTR([A list (Checklist.xlsx)].[email A])=ATTR([A list (Checklist.xlsx)].[email A]) and max([email C])=max([email C]) then 'All 3 Lists'

          elseif isnull(ATTR([B List (Checklist.xlsx)].[email B])) and max([email C])=max([email C]) then 'A + C Only'

          elseif max([email C])=max([email C]) and isnull(ATTR([A list (Checklist.xlsx)].[email A])) then 'B + C Only'

          end

           

          Hope this helps!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Checking presence across multiple list
            Xavier P

            Thanks Tracy! Appreciate you taking the time to review.  I have another question though, minister@britain.com is only on B list and as such not showing on my C list - also my master list.

             

            I guess I need to create an extract with multiple tables in order to have all email addresses from each list - not a must-have but could be nice to.  If so, I also guess I need to change the calculation - will try that myself.  Note to me: was not aware of Max function.