No empty values selection in filter table

[KirstenKa]

It is not possible to select for empty values in the filter in the table, how to resolve this?

[Zapparoli]

Hi @KirstenKa 

This is a null value on Qlik, you can't select it.

What you can do is treat them on your script:
NullAsValue *;
Set NullValue = 'NULL';

With this all the values that you can't select will appear with the text 'NULL' (Or anything you put in the Set NullValue variable).

Hope this helps.

-Zapparoli.

[KirstenKa]

Thanks @Zapparoli   If my code looks like below, how should I add your code in it?

Infa:
LOAD
    "Trading Code" & '|' & "Color Code"		as %ItemColor_GlobalId,	
    "Trading Code" & '|' & "Color Code" 	as ItemColor_GlobalId_Infa,
     Date(Date#("Regional Retail Introduction Date", 'DD/MM/YYYY'),'MM/DD/YYYY') as RegionalRetailIntroductionDate
 
FROM [lib://QlikStorage/1. Development/Private Data/0. Data/1. User Input/ArticleStatusReport.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

[Zapparoli · Accepted solution]

Hi @KirstenKa 

You can add on the top of your script page:

NullAsValue *;
Set NullValue = 'NULL';

Infa:
LOAD
    "Trading Code" & '|' & "Color Code"		as %ItemColor_GlobalId,	
    "Trading Code" & '|' & "Color Code" 	as ItemColor_GlobalId_Infa,
     Date(Date#("Regional Retail Introduction Date", 'DD/MM/YYYY'),'MM/DD/YYYY') as RegionalRetailIntroductionDate
 
FROM [lib://QlikStorage/1. Development/Private Data/0. Data/1. User Input/ArticleStatusReport.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

[KirstenKa]

Thanks for the clarification. When I add this on top op my load code and I search for NULL in my table nothing returns. 

[Zapparoli]

Hi @KirstenKa 

Can you confirm to me if you are loading the  "Item Trading Code" Field AFTER you specify the NullAsValue * ?

In the code you have provided I can't see the "Item Trading Code".

Other alternativa you can do is using a function to treat this value directly like so:

 If(IsNull("Item Trading Code"), 'Null') as [Item Trading Code]

-Zapparoli

[marcus_sommer]

NULL is a special construct which only exists in data-bases respectively it could be the result of a function or a join-statement or similar measures. This means flat-files like csv or xlsx have no NULL else it are EMPTY values and therefore no measurements to fetch NULL per isnull() or with NULL variables will work.

The most robust solution - regardless of the data-source - is a query like:

if(len(trim(MyField)), MyField, 'no value') as MyField

[KirstenKa]

@Zapparoli  yes I load Item Trading Code after Nullasvalue. I applied your other code but now all the values are Null not only the empty ones 🙂

[KirstenKa]

Thanks @marcus_sommer  I applied the code but what name should I search for?  "0" "-" or "no value" doesn't work. These are approx 10000 rows with unique values, takes quite some to scroll through all

[marcus_sommer]

The above shown logic will work by direct loading field-values. If these field-values are partly created with joins or similar measures this kind of logic needs to be applied on this result again.

But your NULL within the UI might be the result of an association between tables. In such cases you will need bigger changes within the data-model by using another kind of data-model which didn't need this association and/or the missing data needs to be populated.

It's not trivial but essential for all kind of data-processing and therefore I suggest to invest some time and going carefully through: NULL handling in QlikView - Qlik Community - 1484472 which also explains that NULL isn't in general bad or an error and needs not mandatory be replaced with anything.