Translating SQL query to Tableau

Question

I am trying to translate the following SQL query into Tableau:

select store1.name, store1.city, store1.order_date
from store1
where order_date = (select max(store2.order_date) from store2 
                    where store2.name = store1.name
                    and store2.city = store1.city)

I am quite new to Tableau and can't figure out how to translate the where clause as it is selecting from another table.

For example, given the following tables

Store 1:

Name   | City      | Order Date
Andrew | Boston    | 23-Aug-16
Bob    | Boston    | 31-Jan-17
Cathy  | Boston    | 31-Jan-17
Cathy  | San Diego | 19-Jan-17
Dan    | New York  | 3-Dec-16

Store 2:

Name   | City      | Order Date
Andrew | Boston    | 2-Sep-16
Brandy | Miami     | 4-Feb-17
Cathy  | Boston    | 31-Jan-17
Cathy  | Boston    | 2-Mar-16
Dan    | New York  | 2-Jul-16

My query would return the following from Store 1:

Name   | City      | Order Date
Bob    | Boston    | 31-Jan-17
Cathy  | Boston    | 31-Jan-17

Carmen S Carmen S · Accepted Answer · 2017-02-01T17:42:33

Point for point, converting that SQL query into Tableau Custom SQL Query would be:

SELECT [Store1].[Name], [Store1].[City], [Store1].[Order Date]
FROM [Store1]
WHERE [Order Date] = (SELECT MAX([Store2].[Order Date]) FROM [Store2]
                      WHERE [Store2].[Name] = [Store1].[Name]
                      AND [Store2].[City] = [Store1].[City])

In the preview you will notice it will only return Cathy. But once you join the SQL Query onto your primary table on Order Date, you will see both Bob and Cathy as you expect.

Translating SQL query to Tableau

1 Answers