How to handle nulls caused due to table association?

[Sivapriya_d]

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 :

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,

[MK_QSL · Accepted solution]

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

or 

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

[Chanty4u]

Try this 

=Alt(Department, 'No Department')

Or

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

[adilio_silva]

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]

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')

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;

[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;