Mapping of data with eliminating NULL values

qlikuser22 — Mon, 21 Feb 2022 10:12:33 GMT

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.

Re: Mapping of data with eliminating NULL values

chris_djih — Tue, 22 Feb 2022 07:18:49 GMT

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

topic Mapping of data with eliminating NULL values in App Development

Mapping of data with eliminating NULL values

Re: Mapping of data with eliminating NULL values