Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
SL2
Contributor III
Contributor III

refer another table in load script

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.

Labels (1)
1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

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];

 

View solution in original post

2 Replies
GaryGiles
Specialist
Specialist

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];

 

View solution in original post

SL2
Contributor III
Contributor III
Author

Thanks it works!