topic Re: vLookup in qlikview in QlikView

vLookup in qlikview

Fri, 20 Nov 2020 18:02:30 GMT

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....

Re: vLookup in qlikview

Wed, 19 Aug 2015 15:17:50 GMT

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

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

Re: vLookup in qlikview

Wed, 19 Aug 2015 15:20:05 GMT

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

Re: vLookup in qlikview

Anonymous — Wed, 19 Aug 2015 15:24:21 GMT

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

Left Join(Table2)

load EmpID,

email,

MobileNo

Resident Table1;

Drop Table Table1;

Re: vLookup in qlikview

sunny_talwar — Wed, 19 Aug 2015 15:24:29 GMT

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..

Re: vLookup in qlikview

Wed, 19 Aug 2015 15:24:48 GMT

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

Re: vLookup in qlikview

Anonymous — Wed, 19 Aug 2015 15:27:45 GMT

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

Left Join(Table2)

load EmpID,

email,

MobileNo

Resident Table1;

Drop Table Table1;

Re: vLookup in qlikview

Wed, 19 Aug 2015 15:27:53 GMT

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..

Re: vLookup in qlikview

Wed, 19 Aug 2015 15:45:33 GMT

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

Re: vLookup in qlikview

sunny_talwar — Wed, 19 Aug 2015 15:49:56 GMT

May be this:

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

Re: vLookup in qlikview

Wed, 19 Aug 2015 16:08:50 GMT

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

Re: vLookup in qlikview

Wed, 19 Aug 2015 16:26:09 GMT

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

Re: vLookup in qlikview

Wed, 19 Aug 2015 16:35:06 GMT

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

Re: vLookup in qlikview

sunny_talwar — Wed, 19 Aug 2015 17:12:35 GMT

Maybe this:

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

Re: vLookup in qlikview

Thu, 20 Aug 2015 08:07:21 GMT

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

Re: vLookup in qlikview

Thu, 20 Aug 2015 08:09:37 GMT

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

Re: vLookup in qlikview

Peter_Cammaert — Thu, 20 Aug 2015 08:30:21 GMT

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

Re: vLookup in qlikview

Thu, 20 Aug 2015 15:05:51 GMT

hi all,

thanks for the response