Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I am getting "field not found (Qty)" when I reload this script:
T1:
Load
ProductID,
Price
From ...
T2:
Load
OrderID,
ProductID,
Qty
From ....
Inner Join Load Qty * Price as SalesPrice from T1;
----------------------
How can I join two tables on a calculated column?
Thanks
QlikView join is a bit different from a SQL join that we are used to... In SQL, when you join 2 tables, you can instantly use fields from both tables. in QlikView, when you join, it's more like "pouring" data from one bucket into another. During the join load, only the fields from the "source" table are available to you. So, to overcome this limitation, you need to first join the two tables, and then reload the result in memory ("resident load") and make the calculation there.
On the other hand, if the whole purpose of your Join is to bring in a single field (like a price), you are better off using MAPPING load and ApplyMap, to grab the price for each Product, without using the join - it's faster in most cases, and certainly "cleaner" than a join.
cheers,
Oleg
QlikView join is a bit different from a SQL join that we are used to... In SQL, when you join 2 tables, you can instantly use fields from both tables. in QlikView, when you join, it's more like "pouring" data from one bucket into another. During the join load, only the fields from the "source" table are available to you. So, to overcome this limitation, you need to first join the two tables, and then reload the result in memory ("resident load") and make the calculation there.
On the other hand, if the whole purpose of your Join is to bring in a single field (like a price), you are better off using MAPPING load and ApplyMap, to grab the price for each Product, without using the join - it's faster in most cases, and certainly "cleaner" than a join.
cheers,
Oleg
please try
T1:
Load
ProductID,
Price
From ...
T2:
Load
OrderID,
ProductID,
Qty
From ....
Inner Join Load Qty * Price as SalesPrice resident T1;