Trouble created a derived field from joined tables

[#1]

Hi guys. I've got 3 different tables I'm joining together using left join loads. So I'm doing something like this:

MyTable:

LOAD A, B, C from src1;

LEFT JOIN LOAD B, D, E from src2;

LEFT JOIN LOAD C,F,G from src3;

Now, I wanted to create a derived field based on two fields in the src2 and src3 tables. So I want to do something like:

LOAD if(D-F < 0, 'True','False') AS derivedfield;

But wherever I put that statement, it seems to lose track of one or the other field from src2 or src3. Any ideas? Thanks for the help!

[Anonymous]

After your create the MyTable:

JOIN (MyTable) LOAD
 A, //(or whatever the primary key is)
 LOAD if(D-F < 0, 'True','False') AS derivedfield
RESIDENT MyTable;

[Oleg_Troyansky]

Michael's suggestion works, however I've been running into some random issues with left join, so I prefer to avoid it every time I possibly can. I usually do it in the"old fashionned way":

MyTable1:
load
 *,
 if(D-F < 0, 'True','False') AS derivedfield
resident MyTable
;
drop table MyTable;

The "*" in your derived load does the trick - you load all existing fields, plus the new one...

cheers,

Oleg