Select Nulls

[agsearle]

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?

[#2]

Hi Andrew,

do you have a short example you can share?

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

Rainer

[agsearle]

I listed a short example?

[agsearle]

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

[#5]

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

[#6 · Accepted solution]

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.

[agsearle]

Thanks Ivang, that seems to have done the job!

[#8]

User NullAsValue Lock_Reason; before the load statement