5 Replies Latest reply on Apr 26, 2012 4:48 AM by toddsherwood

How does one find rows from Table A that are not in Table B?

toddsherwood Apr 17, 2012 11:34 AM

How in Tableau 7.0 does one find rows from Table A that are not in Table B where Tables A and B are joined on a common Integer Key Field 1? To make life a little more complicated, Table B is also indexed by a 3 character Field 2. Field 2 is present in Table A, but is imbedded in a string that may contain multiple Field 2 instances (e.g. "ABC DEF GHI" represents 3 instances of Field 2). It is also desired to find rows in Table A that have Field1/Field 2 instances not present in Table B.

Table A turns out to be an external customer problem report database. Key Field 1 is the problem number. Non-Key Field 2 is a space-separated list of customer site(s) where the problem was hit. Table B is an internal company database. Key Field 1 is the problem number, and Key Field 2 is used to track data on a per customer site basis.

The customer finds new problems and finds existing problems a new sites.

The task is to identify new problems and new instances of existing problems in the customer database so the issues can be added to the company database.

All insights will be very much appreciated!

1. Re: How does one find rows from Table A that are not in Table B?

Tracy Rodgers Apr 18, 2012 8:52 AM (in response to toddsherwood)

Hi Todd,

Would it be possible to post a sample? Table A and Table B, Field 1 and Field 2 are hard for me to keep straight!

-Tracy
Like Show 0 Likes(0)

Actions
2. Re: How does one find rows from Table A that are not in Table B?

toddsherwood Apr 19, 2012 8:08 AM (in response to Tracy Rodgers)

Thanks for responding, Tracy. Here are sample data.

Table A (in comma separated format: field 1, field2, [other fields])
row 1     : 11111, "ABC DEF GHI", [other data]
row 2     : 22222, "GHI JKL", [other data]
row 3     : 33333, "ABC GHI", [other data]

Table B (again in csv format, field 1, field 2, [other fields]
row 1     : 11111, "ABC", [other data]
row 2     : 11111, "DEF, [other data]
row 3     : 11111, "GHI", [other data]
row 4     : 22222, "GHI", [other data]
row 5     : 22222, "JKL", [other data]
row 6     : 33333, "ABC", [other data]
row 7     : 33333, "GHI", [other data]
Like Show 0 Likes(0)

Actions
3. Re: How does one find rows from Table A that are not in Table B?

Keith Fortowsky Apr 19, 2012 8:20 PM (in response to toddsherwood)

There is a simple generic answer:
1. Do a left join of TableA and TableB (i.e. result should have one row for each row in TableA)
2. As part of the result table (which we will call TableC), include one field (which we will call FieldB) consisting of any field from TableB that has no null values in TableB ( ex TableB's primary key field)
3. Filter TableC to select only rows with a null in FieldB - this is the result you are looking for.

/Keith F.
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
4. Re: How does one find rows from Table A that are not in Table B?

Keith Fortowsky Apr 25, 2012 8:15 PM (in response to toddsherwood)

Hello Todd. Is my response suggesting a join understandable to you, or do I need to add more detail?
/Keith F.
Like Show 0 Likes(0)

Actions
5. Re: How does one find rows from Table A that are not in Table B?

toddsherwood Apr 26, 2012 4:48 AM (in response to Keith Fortowsky)

Thanks, Keith! Your answer was very helpful.
Like Show 0 Likes(0)

Actions