look up in 2 tables

[#1]

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!

[MayilVahanan · Accepted solution]

Hi

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;

[tamilarasu]

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

[#4]

It works! Thanks so much!

[#5]

Thanks for your help too!