Custom Joins with the Custom SQL Option In Tableau

The Custom SQL option in the data connection dialog boxes is necessary only in certain specialized circumstances. For most situations, the Single Table or Multiple Tables option is appropriate.
Every database has slightly different SQL syntax. Custom SQL written for one data source may not work with another. If you are not well versed in SQL, you may want to seek assistance from your database administrator when building these connections.
The four main types of SQL joins are described in this article: Inner, Outer (Left and Right), Union, and Self.

Note: For Microsoft Excel and text file data sources, Custom SQL is available only when using the legacy connection or in workbooks that were created before Tableau Desktop 8.2.

Inner join

An inner join returns data if a given value for the key exists in both tables. Unless you have the query prebuilt, we recommend that you use the Multiple Tables option and assemble inner joins. Refer to the Joining Excel Worksheetsarticle for an example of an inner join.
If the relation between these tables is one-to-many or many-to-many, note that your measures may appear to double in Tableau. This is a normal consequence of a join where the relationship is not always one-to-one.
Count Distinct can be helpful when building views after this type of join (Measure > Count (Distinct)).

Outer joins (left and right)

Left joins return all data from the table on the left side of the join, and data from the right side only where a given key value occurs in both tables. Right joins return all data from the table on the right side of the join, and data from the left side only where a given key value occurs in both tables. Unless you have the query prebuilt, we recommend that you use the Multiple Tables option to assemble outer joins.
If the relation between these tables is one-to-many or many-to-many, note that your measures may appear to double in Tableau. This is a normal consequence of a join where the relationship is not always one-to-one.
Count Distinct can be helpful when building views after this type of join (Measure > Count (Distinct)).

Union

You have two tables that contain the same field names and number of fields, but represent different events or entities. You want to combine them into one table, but you also want to add a field that allows you to track the original tables.
A Union allows you to "stack" two tables with the same number of fields and field types, essentially appending table 2 to the end of table 1. When the union is complete, all the field names are the same. During the union, you can add a constant field that denotes which table the data originally came from.
This example uses an Excel workbook that contains two tables: Actual Date and Forecast Date. The Excel file is attached to this article.

Step 1

Connect to your data source. This example uses the attached data source. 

Step 2

In the Connect to Data dialog box, select Microsoft Excel.

Step 3

In the Excel Workbook Connection dialog box, do the following tasks:
  1. Select Browse and browse to the location where you saved the Excel file.
  2. UnderStep 2, select Custom SQL and modify the query to the following:
SELECT [Actual$].[A Date] AS [CompDate],
[Actual$].[Equip $] AS [Equip $],
[Actual$].[OrderID] AS [OrderID],
[Actual$].[Proj Mgr] AS [Proj Mgr],
"A" as [Type]
FROM [Actual$]
UNION
SELECT
[Forecast$].[F Date] AS [CompDate],
[Forecast$].[Equip $] AS [Equip $],
[Forecast$].[OrderID] AS [OrderID],
[Forecast$].[Proj Mgr] AS [Proj Mgr],
"F" as [Type]
FROM [Forecast$]
3. In the dialog box under Step 3, keep the default setting, and click OK.
The "A as Type" and "F as Type" parts of the SQL create a new field named [Type] to differentiate the Actual rows from the Forecast rows. The SELECT sections contain the fields you want from both tables.

Step 6

From the Dimensions pane, drag CompDate to the Columns shelf, then do the following tasks:
  1. Click the plus sign on the YEAR field to add QUARTER.
  2. Click the plus sign on the QUARTER field to add MONTH.
  3. Drag YEAR and QUARTER down and off the Columns shelf to remove them, so that only MONTH is left.

Step 7

Drag Proj Mgr and Type fields to the Rows shelf.

Step 8

From the Measures pane, drag Equip $ to Text.

Create a calculated field for Actual Equip $

This formula tells Tableau to insert the value for [Equip $] for a given row when that row's [Type] is ‘A'. If the [Type] is ‘F', this column is left blank.

Step 1

Select Analysis > Create Calculated Field.

Step 2

In the Calculated Field dialog box, make the following selections to create this formula:
IIF ([Type] = 'A', [Equip $], NULL)
  1. In the Name text box, type Actual Equip $.
  2. Press the Tab key to move the cursor to the Formula text box.
  3. In the Functions list, double-click IIF.
  4. In the Fields list, double-click Type.
  5. Space once, type ='A',, and space again.
  6. In the Fields list, double-click Equip $.
  7. Type a comma, space, and then type NULL.
  8. When finished, click OK.

Create a calculated field for Forecast

This formula creates a second column within Tableau, this time leaving the ‘A' rows blank and entering the given [Equip $] value for the ‘F' rows.

Step 1

Select Analysis > Create Calculated Field.

Step 2

In the Calculated Field dialog box, make the following selections to create this formula:
IIF( [Type] = "F", [Equip $], NULL)
  1. In the Name text box, type Forecast Equip $.
  2. Press the Tab key to move the cursor to the Formula text box.
  3. In the Functions list, double-click IIF.
  4. In the Fields list, double-click Type.
  5. Type ="F",, followed by a space.
  6. In the Fields list, double-click Equip $.
  7. Type a comma, space once, and then type NULL.
  8. When finished, click OK.

Create a calculated field for loan-to-value (LTV) ratio

To calculate LTV is relatively straightforward. LTVis created by dividing Actual by Forecast. Because a cell may represent multiple rows in Tableau, aggregate the values using SUM.

Step 1

Select Analysis > Create Calculated Field.

Step 2

In the Calculated Field dialog box, make the following selections to create this formula:
SUM([Actual Equip $]) / SUM([Forecast Equip $])
  1. In the Name text box, type LTV.
  2. Press the Tab key to move the cursor to the Formula text box.
  3. In the Formula list, double-click SUM.
  4. In the Fields list, double-click Actual Equip $.
  5. Click outside the closing parenthesis and type a forward slash.
  6. In the Formula list, double-click SUM.
  7. In the Fields list, double-click Forecast Equip $.
  8. When finished, click OK.

Update the view

Step 1

On the Rows shelf, remove the Type and Equip fields from the view.

Step 2

From the Measures pane, drag Actual Equip $ to Text.

Step 3

Drag the Measures Names field to the Columns shelf.

Step 4

Drag the Measure Values field to Text.

Step 5

Remove all the fields from the Measures Values card except for the Actual Equip, Forecast Equip, and LTV fields.

Step 6 (optional)

On the Measure Values card, right-click AGG(LTV), select Format, and do the following tasks: 
  1. In the Numbers list, select Percentage. Keep the default of two decimal places.
  2. Click outside the Numbers format window to close it.

Step 7 (optional)

For Tableau Desktop 7.0 and later, select Analysis > Table > Row Grand Totals.
For Tableau Desktop 6.1 and earlier, select Table > Row Grand Totals.

Self Joins

A self join is joining a table to itself; for example, to find all possible pairings of items in a table that share a particular characteristic that is specified in another column of the table. Self joins are rarely needed, but if you need one, you can write it in the Custom SQL part of the connection dialog box.