Help with Nulls

[Dijkstra]

Hello,

I'm trying to change the value of nulls to something else that can be used to filter. This data comes from a QVD file. The field that contains nulls, contains nulls due to no action taken on those items ( they will eventually change to something else once an action has been taken).  I found this link  which was very informative but i tried multiple solutions from the document to no avail. 

What i don't quite understand is that whenever i make a new field (in the script or as an expression) the formula does not propagate in the records that are null, it shows " - ".  For instance, the expression isNull(ActionTaken) will return false in a field that that not null, but only " - " in fields that are null.  If i export the table to Excel, the " - " is exported, i copy this cell to a text analyzer i  the UTF-8 encoded is \x2D\x0A\x0A, i'm not sure if that's an artifact of the export process.

I also tried using the NullAsValue statement but no luck.  Using a combination of Len & Trim = 0 will return the same result as above.  This is only one table, no other tables are involved.

Thanks in advance.

[klikgevoel]

Hi @Dijkstra , if I'm reading your problem correctly you want to change null values into a value, i.e. an integer or string?

From what I know, not a Qlik expert myself, is that QS as opposed to QV does not out-of-the-box allows for changing nulls. Anyway, it can be done in a manner by loading the qvd twice, once containing records that don't have nulls in the variables you specify and the second time with records containing nulls.

A solution might look like this, I know that this might be oversimplicated compared to your situation:

Imagine the following dataset with a few records in Sales that have nulls. First load the records containing no nulls in Sales, followed by another load where only nulls from Sales are loaded and given a value of 0 if there are nulls.

[NoNulls]:

Load

Name,

Sales,

Id

From QVD

Where Not IsNull(Sales);

Concatenate(NoNulls)

Load

Name,

If(IsNull(Sales),0) As Sales

Id

From QVD

Where IsNull(Sales);

Hope this might help

[Dijkstra]

Thanks for the help Klikgevoel. I tried what you suggested and it doesn't seem to be working. I first tried what you suggested, but the results are the same. I then tried loading only one table with the condition, and it still loads the nulls.

WHERE NOT isnull([field with nulls]);

Any other thoughts?

[Or]

What you are describing seems quite odd, because IsNull() shouldn't be capable of returning null, only 0 and -1 (false and true). Are you sure these fields are actually null?

One workaround that people often use for clunky working with nulls is instead checking the length of the string in the field. If the length if zero, and assuming the field doesn't naturally have "blank" (0 length but not null) values, this should work by checking len(Value)=0.

[Dijkstra]

Interesting, I see.  Something new i found when adding len([action]):  I will get " - " where the only record is one with a null in field (same behavior i described earlier) in len and isnull. For any records that have changed  (there are now two records, one where field is no longer null, due to an action being taken, and one where field is null) len will show 0, and isnull will show -1.

id	date	action	len	isnull
1	5/10	-	0	-1
1	5/11	action 1	8	0
2	5/10	-	-	-

[Or · Accepted solution]

With no idea what your exact data structure is, it's hard to try and guess what's going on under the hood. This may be an issue where the null values aren't actually being read from a file, but rather being created e.g. through a concatenation.

One thing worth trying in this case would be to create a second load statement over your first one:

Table1:

Load * From YourCurrentLoad;

Table2:

NoConcatenate Load *, isnull(action), len(action)

Resident Table1;

Drop Table Table1;

If the nulls are being generated in some manner other than being directly read, this way should make sure your formulas looking at them are getting evaluated properly, I think.