Both left joins are supposed to come from same database (and are actually coming from single database)
BUT, I need to join on two criteria, so my thinking was that:
- First join will try to find matching fields F2 (which exist in both original table and table which is left joined) and will add field F3 to the table for matching rows
- Second join will try to find maching fields F1 in first database and to appropriate rows will add values from/to F3 field.
So you want something like this:
1) Create F3 based on F2 field.
2) For the records where 1) haven't resulted in a match, use a lookup from F1 field.
If you want to add only one field in total, use a MAPPING aproach instead of joins.
Load your two lookup tables as mapping tables first the create your concatenated table and instantly add F3 field by use of applymap() (or nested applymaps() if you want above logic).
MAPPING LOAD F2, F3 FROM ...
MAPPING LOAD F1, F3 FROM ...
Applymap('MAP1', F2, Applymap('MAP2',F1) ) as F3,
well, this is closer and I understand the principle but it seems it does not work.
Here is another piece of code which is basically the same as above but loads from data files:
MAPPING LOAD F2,
MAPPING LOAD F1,
ApplyMap('MAP1', F2, ApplyMap('MAP2', F1, null())) as F3
The thing is that I need to match field F2 from my data table with matching row in MAP1 and then add F3 field; if there is no match then I have to try to find if the field F1 has a match in MAP2 and then if there is a match add to field F3 and if there is no match then write down null.
But it does not work.
p.s. please note that in my first code example I have concatenated two tables; in this two examples I have already done that and I am loading single database.