Skip to main content
ericm0008
ericm0008
New Participant
May 20, 2021
Solved

Count function

Why does the count() function return a 1 if I pass in a null? so when I run count({null}) this returns a 1 but to me I would logically think 0. Was hoping for someone to explain this to me, Thank you.

mikes0011
mikes0011Answer
Brainy
May 20, 2021

count() intentionally returns the length of an array including nulls.  if you want to get the length of an array excluding nulls, then you would use length().

The Expression Guru
View in thread

4 replies

mikes0011
mikes0011Answer
Brainy
May 20, 2021

count() intentionally returns the length of an array including nulls.  if you want to get the length of an array excluding nulls, then you would use length().

The Expression Guru
    ericm0008
    ericm0008Author
    New Participant
    May 20, 2021

    Thank you, that makes sense so count is also counting the null.

    peter.lewis
    peter.lewis
    Employee
    May 20, 2021

    The one tricky thing about the length() function though is that you may also need to consider if the variable itself is null. For instance, both of these return the same:

    length({"test"})
count({"test"})

    The one below returns 1 for count and 0 for length:

    length({null})
count({null})

    However, this one below actually fails when using length (and returns 1 for count()):

    length(null)
count(null)

    So, if it's possible that the value could be null and not in a list, it's often good practice to pair your length function with a null check like this:

    a!localVariables(
  local!data: null,
  if(
    isnull(local!data),
    0,
    length(local!data)
  )
)

      Terms & ConditionsAccessibility statement