Make a column from a table part of another table

[RodNakamura]

I have added my data sources into data manager and realized I need to use a column from my Products table in my OrderDetails table in order to create a calculated column. I believe I should use the script editor for this. In the “auto-generated section” I have the lines to call both OrderDetails from a .qvd and Products from a table in an .mdb file. What I would like is to have the UnitCost column from the Products table be part of the OrderDetails table. I want to make a Join using ProductID which is on both tables. Do I have to make a new table and use Join? Do I use a mapping? I’ve read multiple community threads but haven’t found exactly how to do it or what would be the advantage of each method. I’m new to qlik scripts but understand how I’d get this done on SQL directly into a database...

Appreciate your help.

EDIT: Additional info, this is from Auto-generated section:

[Products]:
LOAD
[CategoryID],
[ProductID],
[ProductName],
[QuantityPerUnit],
[SupplierID] AS [SupplierID-Suppliers-_empty_.SupplierID],
[UnitCost],
[UnitPrice],
[UnitsInStock],
[UnitsOnOrder];
SQL SELECT `CategoryID`,
`ProductID`,
`ProductName`,
`QuantityPerUnit`,
`SupplierID`,
`UnitCost`,
`UnitPrice`,
`UnitsInStock`,
`UnitsOnOrder`
FROM `Products`;

[OrderDetails]:
LOAD
[Discount] AS [DiscountPct],
[LineNo],
[OrderID],
[ProductID],
[Quantity],
[UnitPrice] AS [OrderDetails.UnitPrice],
[Quantity] * [UnitPrice] AS [Sales],
([Quantity] * [UnitPrice]) * [Discount] AS [DiscountAmt],
([Quantity] * [UnitPrice]) - (([Quantity] * [UnitPrice]) * [Discount]) AS [Net Sales],
(([Quantity] * [UnitPrice]) - (([Quantity] * [UnitPrice]) * [Discount])) - ([Quantity]) AS [Profit]
FROM [lib://QlikData/OrderDetails.qvd]
(qvd);

I'd like to be able to calculate profit by multiplying Quantity by UnitCost, but that column is only available in my Products table.

[NZFei]

Build the mapping table before order details table:

ProductUnitCostMap:
mapping load
ProductID,
UnitCost
From `Products`;

Then add this line to the OrderDetails load script:

applymap('ProductUnitCostMap', ProductID,0)*Quantity as Cost

Fei

[ChennaiahNallani]

Create map table after products tables
Temp:
Mapping load
ProductID,
UnitCost
resident Products;

add below script into OrderDetails table
[Quantity] * APPLYMAP('Temp',ProductID) AS Cost

or
add below script into OrderDetails table
[Quantity] * LookUp('[ProductID]', '[ProductID]', UnitCost, 'Products') as Cost