topic Select Nulls in QlikView

Select Nulls

agsearle — Thu, 08 Oct 2009 20:50:18 GMT

I need to be able to select nulls in a list box. The solution that keeps coming up is to add the bolded text in my script below:

TABLE1:
LOAD CA
FROM [xxx]

TABLE2:
LOAD CA,
IF(LEN(Lock_Reason) = 0, 'No_Lock', Lock_Reason) as Lock_Reason
WHERE EXISTS (CA)
FROM [xxx]

The data in table 2 contains Lock Reasons (codes from A to Z), but not all the CA's in table 1 have one. From a list box I need to select all Lock Reasons including blanks (Nulls) except lock V.

No matter what I do the blanks remain with '-'s. However, if I change the '0' to a '1' for the length, all the locks show 'No_Locks'?

What am I missing?

Select Nulls

Thu, 08 Oct 2009 20:58:27 GMT

Hi Andrew,

do you have a short example you can share?

Hints: Look for "trim" and related functions in QV help.

Rainer

Select Nulls

agsearle — Thu, 08 Oct 2009 21:24:00 GMT

I listed a short example?

Select Nulls

agsearle — Mon, 12 Oct 2009 20:00:19 GMT

Anyone have any ideas how I can get this to work please?

Select Nulls

Mon, 12 Oct 2009 20:19:45 GMT

For select the null values in listbox you have to use the NullAsValue in your script

Select Nulls

Mon, 12 Oct 2009 20:32:44 GMT

A QlikView join where values are missing still leaves the values missing - they do not exist and QlikView does not define them, unlike other databases where it does define them and set them to a NULL value. You need to make them exist, then change the NULL value to your default. Create a single table using a join...

TABLE1:
LOAD CA
FROM [xxx]

left join TABLE1:
LOAD CA,
Lock_Reason
WHERE EXISTS (CA)
FROM [xxx]

NullMap:
mapping LOAD
null(), 'No lock'
AUTOGENERATE 1;

map Lock_Reason using NullMap;
right join (TABLE1) LOAD DISTINCT
*
RESIDENT TABLE1;

The mapping load will change the NULLS.

Select Nulls

agsearle — Mon, 12 Oct 2009 21:59:08 GMT

Thanks Ivang, that seems to have done the job!

Select Nulls

Tue, 13 Oct 2009 05:18:44 GMT

User NullAsValue Lock_Reason; before the load statement