CODEWITHSUNDEEP
mysqltableau-desktop
S. R.
S. R.

Reputation: 61

how to use multiple tables without duplication in tableau

I've trouble understanding how this should work...basically I've 2 main tables, in one I've Revenues, in another Costs.

Revenues table has fields as: P&L (string), Category (string), Products (string), Sold (int), invoiced (int), delivered (int), date (date).

Costs table has: P&L (string), Category (string), Products (string), Costs (int), date (date).

I'd like to use tables together to perform various calcs like margin, for example, at any level (total margin, which means total revenues - total costs, or at Category level for which I should be able to filter any category I have and perform the calc and so on). Problem is, any tentative I've made to use relations or join, resulted in duplications.

The only workaround I was able to perform so far is to leave revenues table as it is, and create many Costs table, 1 for field basically (table1 with category and costs plus date, table2 with products, costs and date etc.). Joining Revenues with one of these tables seems to work but, in this way, I'm not able to create a wider view (one goal is to make a big table in the viz where we could read at once all the data). Plus, another problem I 've seen it appear doing this workaround is that, if I want to split by date costs, but I use the date column from the revenues table, even if the date is the same (I've done a copy/paste between tables basically), tableau doesn't recognize the date correctly, so to split costs, I've to use costs'date column, and to split revenues, I've to use revenues' date columns, which is frankly a pain...

So my question: how could I merge the 2 tables in one, or anyway how could I put all the data together in a working table to perform any kind of calcs,and also how could I use just 1 column for date that works for all the date altogether?

I've upload a file here to understand better what I'm trying to combine. Thank you guys

Data file

ps.: seems that tableau is using sql behind for these tasks so probably someone skilled in this kind of problem in sql could also help...for this I 've tagged sql as well, thanks

Upvotes: 0

Views: 91

Answers (1)

cmcau
cmcau

Reputation: 557

You need to UNION those 2 tables together, but are they really in Google or you just did that to demo it here?

If you're using Excel - both Revenue & Cost must be different sheets in the same XLS file

If you're using CSV - both Revenue & Cost must be different files (hopefully in the same folder)

I would really hope that you're using a database (some form of SQL), but either of the above options, UNION the data and it will work the way you expect :)

Upvotes: 0

Related Questions