Pro's / Con's: Replace Nulls with text in load script

[Anonymous]

Hi

I have heard tell that there can be advantages to replace Nulls with a text string like say '<Null>' in one's load script.

Can anyone advise the pro's / con's of this  ?

Best Regards,   Bill

[Gysbert_Wassenaar]

The obvious pro is that the 'null' values become selectable. A con could be presentation in tables/charts. People might find all the <Null>'s distracting. Then again you could choose another string like '..'

[hic]

Also, there are a number of functions that will work differently - functions that normally disregards NULL, e.g. count(), only(). And you can no longer suppress NULL values in charts.

An alternative is that you load the same field twice, once with NULL values and once without. The you can use different ones in different cases.

HIC

[stevedark · Accepted solution]

Hi Bill,

I would say that with string values it is entirely up to whether you want to see Unknown (or '- Unknown -' so it sorts to the top) in your list boxes.

With numbers, I have seen significant performance improvements when nulls are replaced with zeros in aggregations.  Further performance gains can be obtained by doing set analysis over numeric values where you exclude zeros, eg. sum({<Value={'<>0'}>}Value) .  Obviously, you have to be careful if you are pulling out averages etc.

- Steve