topic How to refer to Null Values in QlikView

How to refer to Null Values

Thu, 01 Nov 2012 21:14:25 GMT

As par tof my data load I am loading a series of small files that have names for variables that are found elsewhere, for example I may have a variable called Region that contains numbers (1,2,3,4...),and the name file would look like ( i am using the order on a dual statement to prevent sorting by the name and use the order the company likes)

Region Name Order

1 Europe 1

2 Africa 2

(the file is tab delimited)

The problem I am having is that in some variables there are Null values given how the data is coded, and that is fine, I wuld like to add a line like

* Other 10

(where * would signify a Null)

This is all analogous to defining a format in SAS in my mind, I do not want to ignore the nulls or show the as nulls, I want to group them into an other category

The question is how do I refer to the Null, I tried using noting and the tab, or a space and nothjing is working

I realize that I can eliminat the nulls in the load, or in the source, but this is simpler inmy mind and more elegant (I am a fan of how SAS uses formats for things like these)

Mario

Re: How to refer to Null Values

Anonymous — Thu, 01 Nov 2012 22:22:22 GMT

try these expression in the script

if (isnull(region)=0, region,unkown)

IF(LEN(TRIM(region)) = 0, 'unknown', region)

Re: How to refer to Null Values

hic — Fri, 02 Nov 2012 12:15:34 GMT

You cannot refer to NULLs as the are. But you can identify them in the script, like srikanthsri1 suggests.

See more on http://community.qlik.com/docs/DOC-3155. There you will find an extensive explanation of how to do it.

HIC

Re: How to refer to Null Values

Fri, 02 Nov 2012 12:50:41 GMT

Thank you all for the Help. I added a per load to code the nulls as something else and then modified my little table.

It would be nice if twit could be done like sas formats, but this is not bad at all