Joining two tables with same fields

[qlikuser22]

Hi

I am struck with a problem where I have two tables and want to extract the data and final result in one table.

Tabl1:

Load 

EmpNo,

EmpName,

City,

Country,

Address

from abc.qvd;

Tabl2:

Load 

EmpNo

EmpCity,

EmpAddr,

EmpCountry,

EmpSalary

from xyz.qvd;

I need to do something like, For each employee number , I want to produce the address, city and country. If the details are not available I have to pick the address, city and country from table2. 

The final table should be,

EmpNo, EmpName, City,Country,Address, EmpSalary

Thanks.

Please do help. 

[Taoufiq_Zarra · Accepted solution]

@qlikuser22  you can for example use :

Tabl1:

Load 

EmpNo,

EmpName,

City,

Country,

Address

from abc.qvd;

left join

Load 

EmpNo

EmpCity,

EmpAddr,

EmpCountry,

EmpSalary

from xyz.qvd;

Final:
noconcatenate

load EmpNo, EmpName, if(len(trim(City))=0,EmpCity,City) as  City,if(len(trim(Country))=0,EmpCountry,Country) as  Country , if(len(trim(Address))=0,EmpAddr,Address) as Address,  EmpSalary resident Tabl1;

drop table Tabl1;

[qlikuser22]

@Taoufiq_Zarra  It worked perfectly. I was struggling a lot in this. There is so much to learn. Thanks a lot 🙂 

[qlikuser22]

HI @Taoufiq_Zarra 

I made some modifications to the above code. Now the addr,city,country is available in the database but it is not reflecting in qliksense.'

The changes I perferomed is,

I wrote a mapping load  and tried to use apply map to get the details.  I can see the data is available in the raw file but in qliksense I am getting NULL values. 

MapTable:
Mapping Load 
EmpNo,City
from abc.xlsx;

Tabl1:

Load 

EmpNo,

EmpName,

Applymap('MapTable',EmpNo,'NA') as City,

Country,

Address

from abc.qvd;

left join

Load 

EmpNo

EmpCity,

EmpAddr,

EmpCountry,

EmpSalary

from xyz.qvd;

Final:
noconcatenate

load EmpNo, EmpName, if(len(trim(City))=0,EmpCity,City) as  City,if(len(trim(Country))=0,EmpCountry,Country) as  Country , if(len(trim(Address))=0,EmpAddr,Address) as Address,  EmpSalary resident Tabl1;

drop table Tabl1;

Thanks in advance!!