vLookup in qlikview

[#1]

Hi Experts,

I have two tables.

Table1:

load EmpID,

       firstname,

       lastname,

       email,

       JobTitle,

       MobileNo

Table2:

load EmpID,

       fname,

       lname,

       Department,

       StartDate,

       Lengthofservice, etc..

I need to lookup table1 (using EmpID) and bring up the email and MobileNo from Table 1 to Table2 and then i have to drop Table1.

I m using the following in the script:

Table2:

load EmpID,

      Lookup('email','EmpID',EmpID,'ADEXPORT') AS email,

     Lookup('MobileNo','EmpID',EmpID,'ADEXPORT') AS MobileNo,

       fname,

       lname,

       Department,

       StartDate,

       Lengthofservice, etc..

at the end of the script, i did DROP TABLE Table1;

but i m getting null values in the email and MobileNo of Table2.

Am i doing wrong?

Can someone help on this plssssss....

[#2]

seems to be the 4th parameter table name is not exactly same as first table.

Lookup('email','EmpID',EmpID,'Table1') as email

[#3]

You can also Join or ApplyMap to get the desired value.

[Anonymous]

You could use a left join to achieve that same result.

Left Join(Table2)

load EmpID,

       email,

       MobileNo

Resident Table1;

Drop Table Table1;

[sunny_talwar · Accepted solution]

If you need email and mobileno. only you can use MappingLoad also:

Mapping:

Mapping

LOAD EmpID,

       email & '|' MobileNo as Field

Table2:

load EmpID,

       SubField(ApplyMap('Mapping', EmpID), '|', 1) as email,

       SubField(ApplyMap('Mapping', EmpID), '|', 2) as MobileNo,  

       fname,

       lname,

       Department,

       StartDate,

       Lengthofservice, etc..

[#6]

thanks dathu

i did the same, but still null values on email and mobileno.

how to use join or Applymap in this case? pls provide an ex

[Anonymous]

Thats the code U should use in script for join the tables.

Left Join(Table2)

load EmpID,

       email,

       MobileNo

Resident Table1;

Drop Table Table1;

[#8]

Lookup function must return the values if it found exact match. Please apply trim function to truncate any extra spaces on your data.

Table1:

load Trim(EmpID) AS EmpID,

       firstname,

       lastname,

       email,

       JobTitle,

       MobileNo

;

load EmpID,

      Lookup('email','EmpID',Trim(EmpID),'ADEXPORT') AS email,

     Lookup('MobileNo','EmpID',Trim(EmpID),'ADEXPORT') AS MobileNo,

       fname,

       lname,

       Department,

       StartDate,

       Lengthofservice, etc..

[#9]

Hi sunny,

Its working now. thanks

but when the field value in MobileNo or email is empty, its returning '-'.

i did corrected like this

SubField(ApplyMap('Mapping', EmpID.' '), '|', 1) as email,

  SubField(ApplyMap('Mapping', EmpID,' '), '|', 2) as MobileNo, 

Now my email field is empty on the cells with no value, but MobileNo field is still showing empty cells as '-'.

How can i correct this? pls help

[sunny_talwar]

May be this:

If(Len(Trim(SubField(ApplyMap('Mapping', EmpID), '|', 2))) = 0, ' ', SubField(ApplyMap('Mapping', EmpID), '|', 2)) as MobileNo,