topic Multiple lookup in QlikView

Multiple lookup

Fri, 04 May 2012 13:08:19 GMT

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.

Multiple lookup

Fri, 04 May 2012 13:18:10 GMT

You could try the exists function.

For instance when loading the second table you could use:

where exists(Employee Code,Sales_responsible_Employee code)

Multiple lookup

gandalfgray — Fri, 04 May 2012 13:22:34 GMT

and if you want to check either table:

where exists(Employee Code,Sales_responsible_Employee code)

or exists(Employee Code,Delivery_responsible_Employee code)

Re: Multiple lookup

Fri, 04 May 2012 13:58:51 GMT

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

Multiple lookup

Fri, 04 May 2012 14:06:11 GMT

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

Multiple lookup

Mon, 07 May 2012 04:46:12 GMT

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 🙂