How to Link Master Table Using the Missing Field as a Join Clause as a Result of Full Outer Join

Published: 15 Feb 2024
Last Modified Date: 16 Feb 2024

Question

Assume that we have the following data.

User-added image
How can we join table1 and table2 with Date and Product_code fields as Full Outer join, and link master to the result such as follows?

Environment

Tableau Desktop

Answer

Option 1
Follow along in the sample packaged workbook found in the Attachments section to review the steps below.

1. In the Data Source page, add table1 to the canvas.
2. Right-click table1 on the canvas and select Open.
3. Add table2 to the physical layer. Configure the Join setting as follows.
　 User-added image

4. Add master to the physical layer. Click the left side of join clause and select Edit Join Calculation.
　 User-added image

5. Enter the following formula and click OK.

IF ISNULL([Product code]) THEN [Product code (table2)] ELSE [Product code] END

6. Configure the Join setting as follows.
　 User-added image

Option 2
If all the tables reside in a data source that supports custom SQL, use custom SQL to join master with 'OR' condition.
In this example, assume that all the tables exist in Microsoft SQL Server.

1. Connect to the data source.
2. Double-click the New Custom SQL option on the Data Source page.
3. Enter the query like the following.

　 User-added image

SELECT [table1].[Date] AS [Date],
    [table1].[Product_code] AS [Product_code],
    [table1].[Sales] AS [Sales],
    [table2].[Date] AS [Date (table2)],
    [table2].[Product_code] AS [Product_code (table2)],
    [table2].[Profit] AS [Profit],
    [master].[Product_code] AS [Product_code (master)],
    [master].[Product_name] AS [Product_name]
FROM [dbo].[table1] [table1]
FULL JOIN [dbo].[table2] [table2]
    ON [table1].[Date] = [table2].[Date]
    AND [table1].[Product_code] = [table2].[Product_code]
LEFT JOIN [dbo].[master] [master]
    ON [table1].[Product_code] = [master].[Product_code]
    OR [table2].[Product_code] = [master].[Product_code]