Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser22
Creator II
Creator II

Mapping of data with eliminating NULL values

Hi
I am trying to do something and I am struck with the solution. Please it would be great if someone can help. I have data coming from 3 different sources.

Example:

exist_in_flag:
mapping Load
ID,
1 as exist_in_flag
aaaa.qvd;


Table1:
Load ID, InvoiceNo,addr, city , country,amt,
ApplyMap('exist_in_flag',ID,0) as exist_in_flag
from abc.qvd;


Table 2:
Noconcatenate
Load
ID,actual_date,
type,
courseID,
fees

from table2.qvd;


left join(Table2)
load
ID,
addr, city, country, amt
resident Table1
where exists_in_flag = 1;

concatenate
load
ID, addr, city , country
resident Table1

where exists_in_flag=0;

Table3:

Noconcatenate
load *
resident Table2;

left join(Table3)
load
sid as ID,
storename,
storeaddr,
storecity,
storecountry,
startdate
from xyz.qvd;

The ID's are same in all the tables. I want to create a table which has fields like(ID, StoreNo, addr,city,country,amt,actual_date,fees)
So the probblem is, I want to have the Storeaddr,storecity,storecountry to be taken first, if the values are NULL or blank then pick the addr,city,country from table1.
I am trying to create this logic. But I don't know how to proceed.

Basically I want storeaddr,storecity,storecountry to be taken from Table 3 first, if it's not available then select addr,city,country from Table 1 and if the values are NULL in Table 1 then pick the previous value. 

Labels (1)
1 Reply
chris_djih
Creator III
Creator III

Hav a look to these to qlik functions: coalesce() and alt().

If you found help, mark the correct answer and give some likes to ALL contributors, that tried to help.