Coalesce and EmptyIsNull Functions

[Jennell_McIntire]

There are two script and chart functions that I recently became aware of that I will start to use in future scripts. They are: Coalesce and EmptyIsNull. In this blog, I will explain how they both can be used. Let’s start with the Coalesce function.

Coalesce

coalesce(expr1[ , expr2 , expr3 , ...])

According to Qlik Help, the Coalesce “function returns the first of the parameters that has a valid non-NULL representation.” This function can take an unlimited number of parameters. Let’s see it in action. In the table below, there are two fields: FirstName and LastName. The last column uses the Coalesce function to check first the FirstName field and then the LastName field for any non-NULL values. The first one found is returned. If neither the FirstName or LastName fields contain a value, then the last parameter (the default) is returned. In this example, that is “No name provided.”

You can see that when there is a first name in the FirstName field, it is returned and when there is not a first name but just as a last name in the LastName field, the last name is returned as seen with Johnson. The last row in the table did not have a first name or last name, so the default “No name provided” is returned. The first row has an empty string in both the FirstName and LastName fields, so an empty string is returned by the Coalesce function.

In the future, I will use the code below when I want to replace fields that are empty with a value.

I would replace expressions like this:

If(Len(Gender)>0, Gender, ‘Unknown’)

With this expression:

Coalesce(Gender, ’Unknown’)

EmptyIsNull

EmptyIsNull(exp )

The EmptyIsNull function converts empty strings to NULL. In the first row of the table below, FirstName and LastName are empty strings. When using the EmptyIsNull function in the last column, you can see that it returns NULL in place of the empty string in the LastName field. Whereas in the other rows (rows 2-6), the last name is returned.

When I have a field that is not 100% populated, I sometimes replace the empty strings with NULL. In the past, I would use an expression like this:

If(Len(Gender)=0, Null(), Gender)     or    If(IsNull(Gender) or Gender=’’, Null(), Gender)

Now, using the EmptyIsNull function, I can shorten my expression to this:

EmptyIsNull(Gender)

The Coalesce and EmptyIsNull functions are less complex and effective for use in chart expressions and in the script. Test them out the next time you need to replace empty strings in a field.

Thanks,

Jennell

[Or]

---

@Jennell_McIntire wrote:

There are two script and chart functions that I recently became aware of

---

These have only been added recently - I believe in the September 2020 version. Coalesce() in particular is one we've been waiting for to stop writing if(isnull()) any time the values aren't numeric (in which case Alt() works).

[wizardo]

@Jennell_McIntire hi'

these are relatively new, but there was a partial solution before that with out using IF

and that was the function ALT()

the problem with this function that its only helpful with numeric values

Daniel