Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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];
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];
Thanks it works!