Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
RodNakamura
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.

Labels (4)
2 Replies
xufei123
Partner - Specialist
Partner - Specialist

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
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