I'm building a dashboard and I have 3 tables that I want to compare against each other. I want to be able to see both the matching and non matching rows in all 3 tables
Table 1 (all rows are unique)
| ID | PRODUCT NAME | REVENUE |
|---|---|---|
| 1 | Name 1 | $100 |
| 2 | Name 2 | $200 |
| 3 | Name 3 | $300 |
Table 2 (Sales on products from Vendor 1)
| ID | PRODUCT NAME | QUARTER | COUNTRY | VENDOR | SALES |
|---|---|---|---|---|---|
| 1 | Name 1 | Q1 2022 | United Kingdom | VENDOR 1 | $250 |
| 2 | Name 2 | Q1 2022 | United Kingdom | VENDOR 1 | $10 |
| 3 | Name 3 | Q1 2022 | Japan | VENDOR 1 | $155 |
| 3 | Name 3 | Q2 2022 | United States | VENDOR 1 | $40 |
| 2 | Name 2 | Q3 2022 | Canada | VENDOR 1 | $100 |
Table 3 (Sales on products from Vendor 2)
| ID | PRODUCT NAME | QUARTER | COUNTRY | VENDOR | SALES |
|---|---|---|---|---|---|
| 1 | Name 1 | Q1 2022 | Sweden | VENDOR 2 | $110 |
| 3 | Name 3 | Q3 2022 | Brazil | VENDOR 2 | $50 |
| 4 | Name 4 | Q1 2022 | United States | VENDOR 2 | $70 |
| 5 | Name 5 | Q2 2022 | Canada | VENDOR 2 | $20 |
| 5 | Name 5 | Q2 2022 | France | VENDOR 2 | $125 |
How do I join these 3 tables in Tableau so that I can build a dashboard that compares them so I can show things like:
distinct count of products sold by vendor 1 that are found and not found in table 1
distinct count of products sold by vendor 2 that are found and not found in table 1
distinct count of products sold by vendor 1 that were also sold by vendor 2
distinct count of products sold by vendor 1 that were not sold by vendor 2 (and vice versa)
Total revenue of each of the above
Total unique sold each quarter in each country
I tried to full outer join table 1 with tables 2 and 3 and then seperately full outer join table 2 and 3 all on the ID field but kept getting weird results. I then joined on amended the full outer join on table 2 and 3 to also join on product name, id, quarter, country which provided better results but then the sales values were showing weird results. I created a relationship between (table 1 with tables 2 and 3) and (Table 2 and 3) using the id column but doesnt give me the right results. Can someone please help? I'm sure I'm over complicating something that should be simple.
Don't use a join for tables two and three. Use a UNION to append the rows from each to make one table representing sales.
It appears that table one has a single row representing each product. So you then just need a one-to-many relationship between your Product table and the union formed from your two sales table. Each product has many sales.
A UNION is appropriate when the records in two tables represent the same type of entity and there is not necessarily a relationship between rows in the two tables. A JOIN is appropriate when specific items in one table are associated with specific items in another table. In this case, a product in the Product table is associated with the corresponding rows in the Sales table.
Last point, normally you would drop the Revenue column from your Product table, and instead calculate that value when needed from the Sales table.