It’s funny how much trouble can be caused by nothing. Because we cannot see it, the absence of a value is often overlooked in a data set. In Qlik, missing values play a key role in representing relationships (or the lack thereof) between entities. It’s important to deal with them appropriately.
When handling common cases of “nothing”, it’s worth consistently addressing zero length strings or other values that might effectively be lacking a value. Knowing your data set is important in identifying values which may require special treatment. For example, traditional NULL values are not present in text files. Missing values come in as spaces or zero length strings and may require conversion to a uniform identity to avoid being wrongly understood in the application.
Here are some handy functions when identifying and converting these troublesome values:
- TRIM(value)
- This function cleans out leading or trailing spaces; a good function to address values coming in from a text file or Excel.
- NULL()
- Returns a null value; this can be used within an IF construct to return a null in place of any value to be interpreted as null.
- ISNULL(value)
- Tests if the value of an expression is NULL and if so, returns True, -1, or False, 0.
- LEN(value)
- Returns the length of the string input; this can be used in conjunction with trim to identify zero length and multiple space strings.
- IF(condition, then, else)
- An indispensable function, especially when dealing with “empty” values. Can be used in combination with any of the above to force values into uniformity.
The following value handling variables can also be used when interpreting the data to address required conversions in Qlik.
- NullDisplay
- Set this variable to determine what is displayed when encountering a NULL value. Note that the value is still NULL.
- NullInterpret
- Set this variable to interpret certain values present in the data as NULL.
- NullAsValue and NullValue
- Using this combination as outlined below can convert NULL to the specified value.
SET NullValue=”<NULL>”; // Sets value for NULL to be converted to
NullAsValue Field1, Field2; // Turns on NullAsValue interpretation for specified fields
As an example using the functions above, various “empty” values can be handled with consistency. Consider fieldname with potential “empty” values:
IF ( LEN( TRIM( fieldname ) ) = 0, NULL() , fieldname ) as MyExample
Any NULL values can be interpreted consistently across the application by turning on NULL substitution (NullAsValue) and setting the NullValue variable:
SET NullValue=”N/A”;
NullAsValue MyExample;
Each business scenario may require a slightly different application of this technique, however the concept is the same. Addressing these “empty” values in the transformation of the data insulates the user from having to handle it in the application layer. In addition, it permits the user to actually select these values and allows more straightforward interpretation of the data leading to quicker results.
Two pitfalls worth being aware of are regarding aggregations and special characters. Special care is required when using aggregations if you’ve converted NULLs to values in order to avoid considering those new values as worth counting or summing. Take care to consider the original field before transformation as relevant to your business problem. Also, for some data sets, there are yet more troublesome characters that can display with “nothing” like tab characters and the like. For a fully comprehensive discussion and deeper dive into this world of nothingness, see this article in the Qlik Community.
The use of these techniques become critically important when joining data via link or bridge tables where sparse relationships exist. These relationships and, more importantly in this context, lack of relationships are represented in a common structure (the link table). NULL values must be interpreted correctly or can be easily overlooked and misrepresented in the application…. More on that in a future post!
