topic Re: Joining two tables with same fields in App Development

Joining two tables with same fields

qlikuser22 — Thu, 13 Jan 2022 12:18:51 GMT

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.

Re: Joining two tables with same fields

Taoufiq_Zarra — Thu, 13 Jan 2022 13:11:24 GMT

@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;

Re: Joining two tables with same fields

qlikuser22 — Thu, 13 Jan 2022 14:18:21 GMT

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

Re: Joining two tables with same fields

qlikuser22 — Fri, 14 Jan 2022 14:50:02 GMT

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