KNOWLEDGE BASE

Returning Values From One Table That Are Not in Another Table


Published: 01 Nov 2016
Last Modified Date: 27 Jun 2017

Question

How to return account values that are included in one table but not in another for a specific range of dates in Tableau Desktop.

Using SQL, the following query would achieve the desired result:
SELECT AccountName FROM Accounts
WHERE AccountId NOT IN (SELECT AccountId FROM LoggingTable WHERE Date > 2016-08-01)

 

Environment

  • Tableau Desktop 10.0.2
  • Tableau Data Engine

Answer

Accounts that are not present in a second table for a given date range can be returned by joining the two tables using a left join and filter the view on NULL values and the specified date range. Use the attached packaged workbook and Excel files and follow the steps below to achieve the desired result.
  1. Create a join between the two data tables.
    1. In Tableau Desktop, connect to the Account Table Extract. Bring in the table with the list of customers/accounts.
    2. Select Add to bring in a second data source and select the Logging Table Extract. Drag the table with usage information to the view. Join the two tables using a left join and "AccountId" as the linking fields.
    3. A left join will return all "AccountId" values from the Account Table Extract and will populate the "AccountId" row from the Logging Table Extract with Null values if there is not a matching account.
  2. If the original Logging Table Extract table only contains rows for the last 3 months than this step is not necessary. If not, we need to also filter to return "AccountId" values that may have data prior to "8/1/2016", but do not for those three months.
    1. Navigate to Sheet 1.
    2. Create a new calculated field. Name the field "!Filter", insert the following formula and click OK.
      IF ISNULL([AccountId (LoggingTable)]) OR [Date] < DATEADD('month',-3,TODAY()) THEN "Show" ELSE "Hide" END
    3. The above expression, when placed on the Filters shelf, will return "Account Name" values that either have a Null value for "AccountId (Logging Table)" or have a date value that is less than the 3 month threshold relative to Today's date.
  3. Build the view.
    1. Drag "Account Name" from to Rows.
    2. Drag "!Filter" to Filters and select "Show".
Did this article resolve the issue?