topic Re: Dealing with null values in raw data in QlikView

Dealing with null values in raw data

Tue, 15 May 2012 09:58:12 GMT

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

Dealing with null values in raw data

Jason_Michaelides — Tue, 15 May 2012 10:09:14 GMT

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

Re: Dealing with null values in raw data

Fri, 30 Nov 2012 13:18:25 GMT

That is genius! Thanks for that