topic Re: Null values in If Statements in App Development

Null values in If Statements

lankmachine — Thu, 28 Mar 2024 15:57:28 GMT

Hi all,

I'm using the following calculated dimension to compare two columns that contain quite a few nulls.

=IF(
IF(ISNULL([t1.col]), 'Val', [t1.col])
=
IF(ISNULL([t2.col]), 'Val', [t2.col]),
0,
1)

The columns are basically both the same and as a result, I'm getting a lot of NULL values where both columns contain null values but I want the expression to print 0 if the two values are equal (and I'd consider them to be equal if they are both null).

I've tried this method, and I've also tried using ALT, as well as LEN(t1.col)<1 but neither of these has corrected the issue.

I found this article:

https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/Scripting/null-value-handling.htm

which says that comparing any two null values with any relational operator will result in a null value. But I thought I could circumvent this issue by converting all of my null values into text, however this doesn't seem to work.

Any help on this would be appreciated, I've tried pulling out each side of the operator in order to confirm that this code:

IF(ISNULL([t1.col]), 'Val', [t1.col])

will in fact convert null values into text and it seems to be working just fine but for some reason the if statement still doesn't work.

Thanks in advance for the help!

Re: Null values in If Statements

Nicolae_Alecu — Thu, 28 Mar 2024 16:14:22 GMT

Hello,

You can try coalesce() function .

Best Regards,

Re: Null values in If Statements

lankmachine — Thu, 28 Mar 2024 16:14:53 GMT

Thanks for your response!

Unfortunately, I tried this as well and it doesn't seem to have worked.

Re: Null values in If Statements

Nicolae_Alecu — Fri, 29 Mar 2024 08:27:38 GMT

If you're doing this into a table, are you sure there are null values? or it can be missing values ?

Can you give more details? Maybe a snapshot of the table?

Until then, take a look at my table:

Re: Null values in If Statements

lankmachine — Fri, 29 Mar 2024 17:32:55 GMT

Mine looks like this:

I didn't know there was a functional difference between it being null or missing in this case. However, I did run the logic that you ran in your table and it looks something like this so I assume it is null:

The logic that replaces the null values works fine but for some reason, the if statement still appears as null.

Re: Null values in If Statements

BrunPierre — Sat, 30 Mar 2024 15:21:51 GMT

@lankmachine How did you convert the nulls to values?

Re: Null values in If Statements

BawejaMedia — Sat, 30 Mar 2024 16:09:25 GMT

Try this out

=IF(
IsNull([t1.col]) AND IsNull([t2.col]), // If both values are null
0, // Set result to 0
If(
If(IsNull([t1.col]), 'Val', [t1.col]) = If(IsNull([t2.col]), 'Val', [t2.col]), // Compare values after handling nulls
0, // If values are equal, set result to 0
1 // If values are not equal, set result to 1
)
)

Also, check out baweja Media.