topic Condition in Join in QlikView

Condition in Join

mazacini — Tue, 05 Jun 2012 07:42:20 GMT

I have a Trans table

Month	InvNo	Item	Val
Jan	1	A	10
Jan	2	B	20
Feb	3	A	15

I have a Rate table

Month	Rate
Jan	0.7
Feb	0.8

I am trying to join the Rate field in the Rate table to the Trans table for Item equal A.

I have tried this code:

LEFT Join (Trans)

LOAD IMonth,

Rate

Resident Rate

where Item='A'

but this doesn't seem to work.

Any ideas?

Re: Condition in Join

Tue, 05 Jun 2012 07:52:21 GMT

You cannot give any conditions on which the join will take place: 'where item = A' does not work.

The join will happen on shared columns, in this case month. This will result in a table, which you can filter afterwards, e.g. by excluding rows with item='A'.

Another option is to filter the Trans table BEFORE joining it:

Trans_Joined:

LOAD

Month,

InvNo,

Item,

Val

Resident Trans

where Item = 'A';

LEFT JOIN

LOAD

Month,

Rate

Resident Rate;

drop table Trans;

Condition in Join

masha-ecraft — Tue, 05 Jun 2012 08:13:44 GMT

You can do it like this:

Left Join (Trans)
Load
Month,
Rate,
'A' as Item
Resident Rate;

/Masha

Condition in Join

mazacini — Thu, 07 Jun 2012 11:21:45 GMT

Hi Masha

That seems to be exactly what I needed.

Joe