Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mazacini
Contributor III

Condition in Join

Hi

I have a Trans table

MonthInvNoItemVal
Jan1A10
Jan2B20
Feb3A15

I have a Rate table

MonthRate
Jan0.7
Feb0.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?

1 Solution

Accepted Solutions
masha-ecraft
Contributor

Condition in Join

You can do it like this:

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


/Masha

3 Replies
Not applicable

Re: Condition in Join

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
Contributor

Condition in Join

You can do it like this:

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


/Masha

mazacini
Contributor III

Condition in Join

Hi Masha

That seems to be exactly what I needed.

Joe

Community Browser