Null values are missing in Listbox

[Anonymous]

Hello, I'm new here in the community and also have little experience with QlikView. Prompt I have a problem with the contents of listboxes ... in a field I have only one content = YES and even null values (shown as "-"). Sometimes I have to filter on those items, which have null values - filtering for value "-". But these value appears not in listbox (screenshot attached).

What am I doing wrong?

Thank you in advance for your help!

[swuehl]

Hi Sabrina,

NULL is not a value, that's why it's not listed in the list box of all field values.

You could replace NULL with a value in the LOAD script:

LOAD

     If(Len(Trim(FIELD)), FIELD, 'No') as FIELD, // assuming 'No' to replace NULL

Or select Yes and then select the excluded on the related field.

Finding NULL

[Anonymous]

Hi Stefan,

thanks for you quick response. Unfortunately that doesn´t work.

My load script looks like:

--------------------------------

Directory;
Siemens_EDI:
LOAD
[IfA Number], [OrgID(SU)], [Name of supplier], City, Country, Version,
[Siemens EDI],
If(Len(Trim([Siemens EDI])),[Siemens EDI], 'N_A') as Siemens_EDI,

-------------------------------

The field "Siemens EDI" contains the NULL values, which I would like to replace with "N_A".

Could you please have a look into my script command and tell me what I have to change?

Thanks a lot 🙂

[swuehl]

You are stating the same field name twice in your LOAD script, 'Siemens_EDI'.

A field name can be only loaded once to the output table.

I suggest removing the first appearance or rename it to Siemens_EDI_orig

Directory;
Siemens_EDI:
LOAD
[IfA Number], [OrgID(SU)], [Name of supplier], City, Country, Version,
[Siemens EDI] as Siemens_EDI_orig,
If(Len(Trim([Siemens EDI])),[Siemens EDI], 'N_A') as Siemens_EDI,

[Anonymous]

Thanks Stefan.

But I´m still not able to get the expected result so that I can filter directly on NULL respect. "N_A", because the value is not shown (see screenshot). My current script:

Directory;
Siemens_EDI:
LOAD [IfA Number], [OrgID(SU)], [Name of supplier], City, Country, Version,
[Siemens EDI] as Siemens_EDI_orig,
If(Len(Trim([Siemens EDI])),[Siemens EDI], 'N_A') as Siemens_EDI

What is the command to set "N_A" for all NULL Values?

[swuehl]

You can use  NullAsValue:

NullAsValue ‒ QlikView‌

See also

NULL handling in QlikView

But maybe Siemens_EDI field does not show NULL, but some other (empty?) value.

Could you post an updated screenshot of a table with the two EDI fields?

[Anonymous]

Attached you will find a screenshot of table Siemens EDI. The field "Siemens EDI" shows the EDI Status of a supplier and value could be YES or NO. This table is matched with our total supplier list (contains more suppliers than table Siemens EDI). So I have a gap and for a lot of supplier not EDI Status. This suppliers are currently shown in QlikView as "-" in field "Siemens EDI".

I do not now if it realy NULL or empty. But I have to filter on such suppliers without status.

[Anonymous]

[swuehl]

I assume it's indeed a missing value, not NULL in your resident table field record.

Have a look at the documents and blog posts I've linked to above. Henric describes nicely how to handle these missing values.

For example, you can select the suppliers with missing Siemens EDI by selecting all values in your EDI list box, then select the excluded values from field supplier. You can automate this using a button with actions, if you like.

[Anonymous]

if you're not sure, if null() or missing or anything else, then define your own label for null and missing in a straight table for example like that: