refer another table in load script

[SL2]

Hi, my load script is 

[Sheet1]:
LOAD
[Employee],
[Employee ID]

FROM sheet1.xlsx

left join([Sheet1])
[(Sheet2)]:
LOAD
[Account ID] as [Employee ID],
[Department],
[Position]

FROM Sheet2.xlsx

I want to add a new field [employee exist in sheet2 Y/N] in sheet1, i.e. when [Employee ID] in sheet1 is not found in Sheet2 and the new field will show 'N'.

and I still want to use left join here since sheet1 is main table.

can anyone help me with this?

Thanks.

[GaryGiles · Accepted solution]

Something like this:

[Sheet1]:
LOAD
[Employee],
[Employee ID]

FROM sheet1.xlsx

left join([Sheet1])
LOAD
[Account ID] as [Employee ID],
[Department],
[Position],

'Y' as [Exists in Sheet2]

FROM Sheet2.xlsx;

Join ([Sheet1])

Load [Employee ID],

          if([Exists in Sheet2]='Y','Y','N') as [employee exist in sheet2 Y/N]

Resident [Sheet1];

drop field [Exists in Sheet2];

[SL2]

Thanks it works!