Join Multiple Excel Workbooks through Custom SQL Query in Tableau

Recently I came across the need of joining multiple excel files in Tableau. I did a hard research on how to do it. However, most of the instructions I found were about how to join two tabs in the same excel workbook. In this post, I will describe how to join multiple worksheets from different workbooks, which spent me about one hour to figure out. Please note the instructions here work for Tableau Desktop 9.0 or later.

Scenario

Suppose we have one Excel workbook that contains one worksheet that would be joined with another worksheet in another workbook. For example, there is a ‘Connections’ worksheet in workbook ‘testjoin01.xlsx’, recording the origin, destination, and travel times.

Connections in 'testjoin01.xlsx'
Connections in ‘testjoin01.xlsx’

We want to join it with the ‘Nodes’ worksheet in workbook ‘testjoin02.xlsx’. The sheet stores latitude and longitude of associated nodes.

Nodes in 'testjoin02.xlsx'
Nodes in ‘testjoin02.xlsx’

Use Custom SQL to Create the Join

Step 1

Open a new Tableau workbook, select Excel in the Connect To a file section. Find the ‘testjoin01.xlsx’ workbook, and click the drop-down arrow next to Open, and select Open with Legacy Connection.

Step 2

Drag the New Custom SQL table from the left pane to the join area.

Step 3

In the custom SQL query field, copy and paste the LEFT JOIN query similar to the one shown below, change and update to your use case.

SELECT 
a.*,
b.[LAT] AS [OR_LAT], b.[LONG] AS [OR_LONG],
c.[LAT] AS [DE_LAT], c.[LONG] AS [DE_LONG]
FROM(([Connections$] AS a
  LEFT JOIN
   [C:\Users\lf\Desktop\testjoin02.xlsx].[Nodes$] AS b 
      ON a.[Orig] = b.[ICLOC])
  LEFT JOIN
   [C:\Users\lf\Desktop\testjoin02.xlsx].[Nodes$] AS c 
      ON a.[Dest] = c.[ICLOC])

In the SQL query above, nested left joins are used to join the Nodes sheet in the second workbook twice, one for the origin, the other for the destination. Please note, the full path of the second workbook has to be specified in the left joins.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s