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!!
Double-click or drag another table to the join canvas. If your next table is from another data source entirely, in the left pane, under Connections, click the Add button ( in web authoring) to add a new connection to the Tableau data source. With that connection selected, drag the desired table to the join canvas.
Beginning with Tableau 6.0, you can use multiple data sources for a single worksheet. One data source is primary; all others are secondary.
Joins v/s Data Blending in Tableau Data Blending Aggregates the data and then combines it. Joins combine the data and then aggregates it. Data Blending can combine data from different sources. Joins can combine data from the same sources only.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With