Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Multiple lookup

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
Valued Contributor

Multiple lookup

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

Re: Multiple lookup

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

Multiple lookup

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

Multiple lookup

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 :-)

Community Browser