topic look up in 2 tables in QlikView

look up in 2 tables

Fri, 19 Jun 2015 02:26:23 GMT

Hi everyone,

I would like to lookup the values between 2 tables, the lookup value in table1 has only part of the value same as table 2, like below:

Table1:

Load * inline [

Name1,ID

Chen Tai Man, 12345

Chan Wai, 23456

Wong Kai, 99999

Table2:

Load * inline [

Name2,Status

Tai Man, A

Wai, B

];

I would like to return the value in table1, for example field ID, when they find a match case.

Is it possible to do it in Qlik? Thanks for your help first!

Re: look up in 2 tables

MayilVahanan — Fri, 19 Jun 2015 04:49:13 GMT

May be try like this

Table2:

Load * inline

[

Name2,Status

Tai Man, A

Wai, B

];

Join

Load * inline

[

Name1,ID

Chen Tai Man, 12345

Chan Wai, 23456

Wong Kai, 99999

];

FinalTemp:

LOAD *, If(WildMatch(Name1, '*'&Name2&'*'),1,0) AS Flag Resident Table2;

NoConcatenate

Final:

LOAD * Resident FinalTemp Where Flag = 1;

DROP Tables Table2, FinalTemp;

EXIT Script;

Re: look up in 2 tables

tamilarasu — Fri, 19 Jun 2015 08:31:11 GMT

Hi Ming,

Read the below link (Similar kind of question) and you might get a idea.

Left Join of 2 tables with a partial string match

Re: look up in 2 tables

Fri, 19 Jun 2015 09:58:35 GMT

It works! Thanks so much!

Re: look up in 2 tables

Fri, 19 Jun 2015 09:58:49 GMT

Thanks for your help too!