Condition in Join

[mazacini]

Hi

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?

[#2]

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;

[masha-ecraft · Accepted solution]

You can do it like this:

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

/Masha

[mazacini]

Hi Masha

That seems to be exactly what I needed.

Joe