Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
You can do it like this:
Left Join (Trans)
Load
Month,
Rate,
'A' as Item
Resident Rate;
/Masha
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;
You can do it like this:
Left Join (Trans)
Load
Month,
Rate,
'A' as Item
Resident Rate;
/Masha
Hi Masha
That seems to be exactly what I needed.
Joe