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

Qlik Cloud Backup

Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more.

Join the team!

Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings.