topic Re: How to handle nulls caused due to table association? in App Development

How to handle nulls caused due to table association?

Sivapriya_d — Wed, 20 Aug 2025 15:01:11 GMT

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,

Re: How to handle nulls caused due to table association?

MK_QSL — Wed, 20 Aug 2025 15:10:34 GMT

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

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

Re: How to handle nulls caused due to table association?

Chanty4u — Wed, 20 Aug 2025 18:03:14 GMT

Try this

=Alt(Department, 'No Department')

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

Re: How to handle nulls caused due to table association?

adilio_silva — Wed, 20 Aug 2025 18:38:26 GMT

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;

Re: How to handle nulls caused due to table association?

savioricardog — Wed, 20 Aug 2025 18:58:03 GMT

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:

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;

Re: How to handle nulls caused due to table association?

marcus_sommer — Thu, 21 Aug 2025 09:21:22 GMT

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;