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

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Sivapriya_d
Creator
Creator

How to handle nulls caused due to table association?

Hi All,

I am trying to handle the null in the front end. Please refer below example.

A:
Load * Inline [
ID,Name
1,xx
2,yy
3,zz
4,aa
];

B:
Load * Inline [
ID,Department
1,Dept1
2,Dept2
3,Dept3
];

My output table :

Sivapriya_d_0-1755701802082.png

For ID - 4 instead of null, I would like to show no department. I tried below options, but doesn't help
=IF(Isnull(Department) or Len(trim(Department))=0, 'No Department',Department)

=Coalesce(Department,'No Department')

Please let me know how this can be solved ? . Note: I dont want to join the tables.


Thanks,

Labels (3)
1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

=Aggr(IF(IsNull(Department) or Len(Trim(Department)) = 0, 'UNKNOWN', Department),Department,ID,Name)

 

or 

 

=Aggr(IF(IsNull(Department), 'UNKNOWN', Department),ID,Name)

View solution in original post

5 Replies
MK_QSL
MVP
MVP

=Aggr(IF(IsNull(Department) or Len(Trim(Department)) = 0, 'UNKNOWN', Department),Department,ID,Name)

 

or 

 

=Aggr(IF(IsNull(Department), 'UNKNOWN', Department),ID,Name)

Chanty4u
MVP
MVP

Try this 

=Alt(Department, 'No Department')

Or

=If(Len(Trim(Department))=0, 'No Department', Department)

 

adilio_silva
Contributor III
Contributor III

To create a new table B with the ids that are not in table A:

A:
Load * Inline [
ID,Name
1,xx
2,yy
3,zz
4,aa
];

B:
Load * Inline [
ID,Department
1,Dept1
2,Dept2
3,Dept3
];

C:
LOAD 'temp' as tbl, ID
RESIDENT A;
LEFT JOIN 
LOAD 'temp' as tbl, ID, Department
RESIDENT B;

New_B:
NoConcatenate
LOAD ID, Coalesce(Department, 'UNKNOWN') as Department
RESIDENT C;

DROP TABLES B, C;

EXIT SCRIPT;

 

savioricardog
Contributor III
Contributor III

Hi @Sivapriya_d 

You can solve this issue with 2 ways:

Creating a measure instead a dimension in your table with this code below:

Coalesce(Department, 'No Departament')

savioricardog_1-1755716171326.png

 

Or solve this issue in your script with this code below:

A:
Load * Inline [
ID,Name
1,xx
2,yy
3,zz
4,aa
];
 
left Join(A)
Load * Inline [
ID,Department
1,Dept1
2,Dept2
3,Dept3
];
 
 
A_B:
load
*,
Coalesce(Department, 'No Departament') as Departament_Transform
resident A;
Drop table A;
 
savioricardog_0-1755715829342.png

 

 

marcus_sommer

NULL isn't equally to an error else it's a valid information. This means it mustn't mandatory be replaced else only if there are special needs to access or count them. If so - it should be done within the data-model.

In your case it would mean to populate the missing data - preferable by adding them per concatenate to the target, for example with something like this:

t1: load * from X;

t2: load *, ID as Check from Y;

concatenate(t2) load ID, 'missing' as Department
resident t1 where not exists(Check, ID);
drop fields Check;