Dealing with null values in raw data

[#1]

Hi All,

I often read in raw data files that contain nulls.  Unfortunately, QView doesn't treat the nulls as nulls but more like blanks.  Therefore, you end up with list boxes containing blanks that can be selected.

To get around this I have to do the following in the load script:

IF(LEN(variable)=0, NULL(), variable) AS variable

This is fine for one or two variables, but when it is almost all variables across multiple tables it becomes tedious. 

How do you deal with nulls?  Is there a simpler more efficent way?

thanks

GPC

[Jason_Michaelides · Accepted solution]

There is a way you can force all blanks in all fields to be NULL.  You need to use MAP....Using -

Early on in your load script:

Map_Nulls:

MAPPING LOAD

     '',

     Null()

Autogenerate 1;

MAP * USING Map_Nulls;

Now, at the end of the script when the fields are commited to the QV data model, all blanks will be replaced with NULLs.  Look up MAP...Using in the F1 help for the different wildcard options you can use.

Hope this helpa,

Jason

[#3]

That is genius!  Thanks for that