Connect two data sources together without Join in Tableau
I have two data sources in tableau (A and B). The relationship is 1:n. Table A (main Table) with columns ID (Primary Key), Field 1, Field 2... . Table B with columns ID (not PK), Field X, Field Y.
I want to use table B for filtering by Field X and Field Y and then in a related sheet plot data from table A with the filter that in SQL would be equivalent to WHERE A.ID IN (SELECT B.ID FROM TableB B) where the Table B would already be filtered by the values of Field X and Field Y.
After some research I have found two options that do not convince me:
Option 1: Use a common inner join between the two tables and then use aggregation functions like AVG and COUNT DISTINCT on the measures of table A to avoid duplication.
Option 2: Use custom SQL on table A based on a parameter that is sent from Tableau and filter Table A at a database level.
The best option would be to have a join between sources in the following structure
SELECT A.* FROM tableA A INNER JOIN (SELECT DISTINCT ID FROM TABLE B WHERE Field X IN (Dynamic selection from Tableau) AND Field Y IN (Dynamic selection from Tableau) ) B ON A.ID = B.ID
Is such thing possible?
Thanks!!
Answer
There is a third option, that can be good enough depending on the sizes of your tables. Use Tableau Data Blending. Create a link between the 2 tables (Data -> Edit Relationships), using the Id field.
Now drag the info you want from table A to the worksheet, then drag field X and field Y to Filter. Voilà.
If the tables are too big (especially table B), you may want to join beforehand. But that could cause the duplicated entries problem (if there are more than one correspondence of each entry in A on B). A Left join is more advisable (and no filters)