Dealing with empty and null values in Qlik Sense

At Bitmetric we continuously hone our skills, and we like to help you do the same. That’s why we regularly post a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about handling null values when loading a CSV file into Qlik Sense. Do you know the answer?

The answers were varied, but overall most people picked (or guessed) the correct answer:

The correct answer is C: EmptyIsNull()

The EmptyIsNull() function yields a null value when given an empty string as input. This functionality proves particularly beneficial when handling CSV files, like we do in this question, where empty fields are read as empty strings instead of null values.

Let’s review the other answers, and why they’re not correct in this context:

• Answer A – Do nothing. This doesn’t work, as empty values in CSV files are not automatically converted to null values when loading data into Qlik.

• Answer B – NullValue. This does the exact opposite of what we’re trying to achieve. The NullValue statement converts null values into a value. In this case an empty string. This can be very useful when profiling data, as it makes null values selectable, but doesn’t help us here.

• Answer D – NullInterpret. This is the “gotcha” option. The NullInterpret statement is designed to treat the given value as null. While this solution might also be effective, it applies universally. In the context of this question, it implies that empty values in the Amount column would be transformed into nulls. This might be desirable in a practical situation, but it’s not the optimal choice for this particular question.

Another great solution was provided by Daniel Pilla, who suggests using the Num() function. This will convert any non-numeric values into null. In a practical sense, this is probably the best way to handle this.

Of course, there’s much more to tell about null values in Qlik, but we’ll save that for another post. We’ll conclude this post with an excellent visual illustration of the difference between empty and null:

The difference between empty and null

Original image by R. Sato (@raysato)

See you next time!

More from the Bitmetric team

Bitmetric Qlik Support

Keep your Qlik environment running smoothly with proactive support that prevents issues before they appear. Available on flexible monthly plans. Learn more.

Qlik vs Power BI Series

See how Qlik and Power BI perform when theory meets reality. Three apps rebuilt, every step examined. Learn more.