Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
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;