3 Replies Latest reply on Feb 21, 2019 11:27 PM by Daniel Bouwmeester

# Question about joining two tables

Hi everybody,

I have a question about joining two tables with data, eventually to be able to make a cost calculation for a production company for packed vegetables which produces bags of vegetables:

• In the example, they produce 1 type of vegetable in bags with different weights
• Some costs are the same for every weight: fixed costs per bag
• Some costs are related to the weight: variable costs per bag
• Also the amount of bags per box are related to the weight and thus the costs per unit for the box itself
• I have one table with fixed costs and one table with variable costs: the table with variable costs has got more columns
• By putting together with an 'Union' a dataset is created in which the value for 'Weight per unit' and 'Amount per box' is 'null' for the rows from table 1
• But the problem is: these costs are valid for all 'weight per unit's.
• If add a worksheet and put the weights in columns, the costs in rows, then I get one column 'Null' with the values of the fixed costs and several columns with the weights with the values of the variable costs
• How can I fix this? Is this possible within Tableau?

Table 1: fixed costs > they are for all the products the same, however I want to be able to calculate a positive and negative scenario

PRODUCT CODESCENARIOTYPE OF COST
CATEGORY OF COSTCOST PER UNIT (EUR)
VEGETABLE01POSITIVETransport inboundTRANSPORT0,0125
VEGETABLE01NEGATIVETransport inboundTRANSPORT0,0150
VEGETABLE01POSITIVECooling costsSTORAGE0,0100
VEGETABLE01NEGATIVECooling costsSTORAGE0,0150
VEGETABLE01POSITIVEProduct labelPACKAGING0,0044
VEGETABLE01NEGATIVEProduct labelPACKAGING0,0085

Table 2: variable costs > they are different for each different bag (200g, 300g, 500g) because they are related to the weight. This influences not only the cost per unit, but also the amound of packagings which can be stored in a cardboard box. Also in this table, a positive and negative scenario is included.

PRODUCT CODEWEIGHT PER UNIT (g)SCENARIOAMOUNT PER BOX

TYPE OF COST

CATEGORY OF COSTCOST PER UNIT (EUR)
VEGETABLE01200POSITIVE90BagPACKAGING0,0025
VEGETABLE01200NEGATIVE90BagPACKAGING0,0028
VEGETABLE01300POSITIVE60BagPACKAGING0,0045
VEGETABLE01300NEGATIVE60BagPACKAGING0,0049
VEGETABLE01500POSITIVE36BagPACKAGING0,0072
VEGETABLE01500NEGATIVE36BagPACKAGING0,0078
VEGETABLE01200POSITIVE90BoxPACKAGING0,0110
VEGETABLE01200NEGATIVE90BoxPACKAGING0,0120
VEGETABLE01300POSITIVE60BoxPACKAGING0,0142
VEGETABLE01300NEGATIVE60BoxPACKAGING0,0153
VEGETABLE01500POSITIVE36BoxPACKAGING0,0170
VEGETABLE01500NEGATIVE36BoxPACKAGING0,0182
• ###### 1. Re: Question about joining two tables

It looks like you have to UNION the 2 tables, then create calculated fields to fill the NULL with your desired value.

Something like:

If isnull([Weight]) then 100 else [Weight] end

• ###### 2. Re: Question about joining two tables

You can do Data blending. Edit relationship and give connection to product code .

• ###### 3. Re: Question about joining two tables

Thanks for your reply Chris, I tried this but they have to be applicable for all possible weights. So not only 200, but also 300 and 500. I think this is not possible in this way?