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]

thanks sunny.

its fine now.

i m trying to add noreply@xxx.com to email field if it is empty and did the script as,

If(Len(Trim(SubField(ApplyMap('Mapping', EmpID), '|', 1))) = 0, 'noreply@xxx.com ',SubField(ApplyMap('Mapping', EmpID), '|', 1)) as email,

but its not giving noreply@xxx.com into empty cells.

what should i do?

pls help

[#3]

You might want to split this into two mapping in that case then, to give you a bit more flexibility in your returned applymap default value. It also just makes the coding a bit simpler.

Mapping_email:

Mapping

LOAD EmpID,

       email

Where Len(email)>0

Mapping_mobile:

Mapping

LOAD EmpID,

       MobileNo

Table2:

load EmpID,

       ApplyMap('Mapping_email', EmpID, 'noreply@xxx.com') as email,

       ApplyMap('Mapping_mobile', EmpID) as MobileNo, 

       fname,

       lname,

       Department,

       StartDate,

       Lengthofservice, etc..

hope that helps

Joe

[#4]

i just want to replace the cells by noreply@xxx.com, if its not empty then should return email

[sunny_talwar]

Maybe this:

If(Len(Trim(SubField(ApplyMap('Mapping', EmpID), '|', 1))) = 0, 'noreply@xxx.com', SubField(ApplyMap('Mapping', EmpID), '|', 2)) as email

[#6]

Hi sunny,

i tried above expression.

but it is returning EmpID when Len(Trim(SubField(ApplyMap('Mapping', EmpID), '|', 1))) = 0

which is not correct.

how can i correct this

or is it possible to do in table?

pls help

[#7]

Sorry I missed the required where clause on the email mapping load, have adjusted now for you.

Mapping_email:

Mapping

LOAD EmpID,

       email

Where Len(email)>0

By reducing this mapping load to only those ID's that have an email, it means that all other values (those without an email) will be assigned the default output noreply@xxx.com‌ (because that is the third parameter set in the applymap)

hope that helps

Joe

[Peter_Cammaert]

That's the default ApplyMap behavior if you don't specify a third parameter. When ApplyMap() now cannot find an entry for EmpID, it will return the value of EmpID itself.

Do not try to combine the two Lookups into a single Mapping table, as the additional logic will substantially decrease performance. The cost of an additional Mapping table is very low. Moreover, mapping tables are eliminated at the end of script execution.

Keep it simple, use the code that Joe suggested. Let QlikView itself help you in handling missing values.

Best,

Peter

[#9]

hi all,

thanks for the response