Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple lookup

Hi Please help me on below.

I have two tables and i need to limit the second table based on first table on 2 fileds.

Table A

Employee Code

Employee Name

Table B

Order Number

Sales_responsible_Employee code

Delivery_responsible_Employee code

amount

What I need is that table B should only load the rows where either sales responsible or delivery responsible is within a given list of employee names from table A (say 'Marc', 'Peter', 'Mike').

Is it possible to make such a script ? If yes, kindly giude me on how can it be done ?

Many thanks in advance,

Devang.

5 Replies
Not applicable
Author

You could try the exists function.

For instance when loading the second table you could use:

where exists(Employee Code,Sales_responsible_Employee code)

gandalfgray
Specialist II
Specialist II

and if you want to check either table:

where exists(Employee Code,Sales_responsible_Employee code)

or exists(Employee Code,Delivery_responsible_Employee code)

Not applicable
Author

Exist and OR may slow down the script performance. You can use right and outer join to load the required data.

Emp:

Load EmpCode, EmpName

from <tablename>;

Order1:

rightjoin(Emp) load

Order Number ,

Sales_responsible_Employee_code as EmpCode,

Sales_responsible_Employee_code as SREC1,

Delivery_responsible_Employee_code as DREC1

amount

from <Ordertable>;

Order2:

noconcatenate(Order1)

rightjoin(Emp)

load

Order Number ,

Sales_responsible_Employee_code as SREC2,

Delivery_responsible_Employee_code as EmpCode,

Delivery_responsible_Employee_code as DREC2

amount

from <Ordertable>;

//Outer Join on Order Number Column

Order:

outer join(Order1) load Order Number, SREC2, EmpCode as EmpCode2, DREC2  from Order2;

You can check the performance of both of the script if you data is very huge and then decide.

Hope this help,
Anosh

Not applicable
Author

You can also use lookup function

where lookup('Emp Code', 'Emp Name','Sales_responsible_Employee code') is not null

or lookup('Emp Code', 'Emp Name','Delivery_responsible_Employee code') is not null

Not applicable
Author

Thanks guys for all your contributions. I think i will try Gandalf and Anosh's solution and that will do the trick. Once again thanks a ton buddies, you rock 🙂