topic Join question in QlikView

Join question

Mon, 25 Jul 2011 15:45:31 GMT

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

Join question

Oleg_Troyansky — Mon, 25 Jul 2011 15:51:18 GMT

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

Join question

perumal_41 — Tue, 26 Jul 2011 04:22:53 GMT

please try

T1:

Load

ProductID,

Price

From ...

T2:

Load

OrderID,

ProductID,

Qty

From ....

Inner Join Load Qty * Price as SalesPrice resident T1;