Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Make a column from a table part of another table

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.

2 Replies
Highlighted
Partner
Partner

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

Highlighted
Creator III
Creator III

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