Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
=Aggr(IF(IsNull(Department) or Len(Trim(Department)) = 0, 'UNKNOWN', Department),Department,ID,Name)
or
=Aggr(IF(IsNull(Department), 'UNKNOWN', Department),ID,Name)
=Aggr(IF(IsNull(Department) or Len(Trim(Department)) = 0, 'UNKNOWN', Department),Department,ID,Name)
or
=Aggr(IF(IsNull(Department), 'UNKNOWN', Department),ID,Name)
Try this
=Alt(Department, 'No Department')
Or
=If(Len(Trim(Department))=0, 'No Department', Department)
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;
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:
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;