Calculation of field from 2 tables

[Anonymous]

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

[morganaaron]

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

Load

Product,

Amount * Cost as TotalCost

Resident Sales;

The TotalCost field gives you that total cost by product.

[rubenmarin]

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:

LOAD ....

     ApplyMap('Costs', Product) as Cost,

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

From SalesTable;

[maxgro]

c:

load * inline [

Product,Cost

AAA,0.05

BBB,0.01

CCC,0.025

DDD,0.035

];

s:

load * inline [

Product,Amount

AAA,100

BBB,200

CCC,150

DDD,300

];

d:

NoConcatenate load * resident c;

join (d) load * Resident s;

left join (d) load Product, Cost*Amount as TotalCost Resident d;

DROP Table c, s;