3 Replies Latest reply: Feb 17, 2015 11:31 AM by Massimo Grossi

# Calculation of field from 2 tables

Hi

I have one table (called 'Sales') which has sales data by product e.g.

Product,Amount

AAA,100

BBB,200

CCC,150

DDD,300

and I have another table (called 'Costs') which has standard costs by product e.g

Product,Cost

AAA,0.05

BBB,0.01

CCC,0.025

DDD,0.035

In the script how can I produce another table which has total costs by product (I want to multiply Amount by Cost for each product)

This is a very simplistic example, my tables in reality are much more complicated but this is a good example to get me started.

Thanks

• ###### Re: Calculation of field from 2 tables

Assuming you have your tables joined (into one fact table), you can create a new table as 'Total Costs':

Product,

Amount * Cost as TotalCost

Resident Sales;

The TotalCost field gives you that total cost by product.

• ###### Re: Calculation of field from 2 tables

Hi Adrian, if you only want to assign the cost of the product, and it's only one cost for each product you can add a mapping table, ie:

MAP_Costs:

Mapping LOAD Product, Cost From CostsTable;

Sales:

ApplyMap('Costs', Product) as Cost,

ApplyMap('Costs', Product)*Amount as TotalCost

From SalesTable;

• ###### Re: Calculation of field from 2 tables

c:

Product,Cost

AAA,0.05

BBB,0.01

CCC,0.025

DDD,0.035

];

s:

Product,Amount

AAA,100

BBB,200

CCC,150

DDD,300

];

d: