Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
RyanProbyn
Contributor II
Contributor II

Updating only non-null fields in join

Hi,

I have a table which i want to update the value of using another table, only if the value from the table is not null.

example:

Table 1:

IDtype
1a
2b
3c

Table 2:

IDtype
1NULL
2d
3e

 

joining the tables should give:

IDtype
1a
2d
3e

 

Kind regards,

Ryan

2 Replies
lironbaram
Partner - Master III
Partner - Master III

hi,
this script should work for you 

Table2:
NoConcatenate load * Inline [
ID,	type
1,NULL
2,d
3,e
]; 
updateType:
mapping load * Resident Table2 where type<>'NULL';

drop table Table2;

Data:
load ID,
     type as OldType,
     ApplyMap('updateType',ID,type) as type;
load * inline [ 
ID,type
1,a
2,b
3,c];

 

RyanProbyn
Contributor II
Contributor II
Author

Hi,

Thank you for your response but unfortunately the suggestion didn't quite work with my data.

I ended up joining my two tables on ID and with differently named type columns. then reloading as follows:

 

Final_Table:
Load id,
if(isnull(Type_Table_2), Type, Type_Table_2) as Type
Resident Table_1_2_joined;

 

Kind regards,

Ryan