Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@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 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;
@Taoufiq_Zarra It worked perfectly. I was struggling a lot in this. There is so much to learn. Thanks a lot 🙂
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!!