Join question

[#1]

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

[Oleg_Troyansky · Accepted solution]

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

[perumal_41]

please try

T1:

Load

ProductID,

Price

From ...

T2:

Load

OrderID,

ProductID,

Qty

From ....

Inner Join Load Qty * Price as SalesPrice resident T1;