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
=> 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?
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.
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.