Replace NULL (non-value) with numerical 0

[datanibbler]

Hi,

I know the issue of 0-values has been here quite a nr. of times, it is always tricky.

In my instance, I have a large table which has one record

- per day

- per employee

=> To this table (which tells me just which employees are assigned to which area in the company), I join several other infos which I load from the database:

- days a specific employee reported out sick

- days an employee was not scheduled for work

and others.

=> I usually just type a 1 in every record for that piece of info, so I can just sum that up in the diagram.

=> These additional tables do not have one record per day - only for those days where e.g. somebody was out sick, not for the others.

I join those to my "daily" main table, so that is that.

<=> The issue is: In all the lines where those additional pieces of info do not apply (someone was not out sick or he was not off_duty etc.), I get a "-" - i guess that represents a non-existent value.

=> Can I somehow, in the process of joining (LEFT JOIN), populate all those records with a numerical 0, just to make it a bit more elegant and one can easily see, when opening the preview of that table, whether or not the formula works?

Thanks a lot!

Best regards,

DataNibbler

[swuehl · Accepted solution]

No. either use a mapping approach, which allows to use a default value, or join and then use a second resident table load to fill in missing values.

[datanibbler]

Thanks!

Knowing that there is no (easy) way to do this is also helpful.

I think an additional RESIDENT LOAD is not necessary here. Let's see. On the other hand, it doesn't take long...

[Anonymous]

Also consider the NullAsValue statement which, in combination with the Set NullValue command allows you to define which fields to look for nulls in and what to replace them with. Might be a bit more elegant than mapping loads.

[datanibbler]

Indeed, that sounds like a more elegant way of solving that issue - I didn't know that command yet, never used it.

I'll read up in the help_file.

Thanks a lot!

Best regards,

DataNibbler

[swuehl]

Not sure if this would simplify things:

SET NullValue = 'Hi';

NULLASVALUE *;

Test:

LOAD * INLINE [

A, B

1,2

2,3

];

LEFT JOIN (Test) LOAD * INLINE [

B,C

2,4

];

won't replace the NULL with a value.

You need another  resident load:

Test2:

NoConcatenate LOAD * Resident Test;

drop table Test;

[datanibbler]

Hi swuehl,

yes and no - it does look more elegant - but I'll still need a RESIDENT LOAD to execute this, so it does not make it easier, compared to an IF-construct I can use to put the 0 in all the non-populated records.

Thanks anyway!