Null values in If Statements

[lankmachine]

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...

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!

[Nicolae_Alecu]

Hello,

You can try coalesce() function .

Best Regards,

[lankmachine]

Thanks for your response!

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

[Nicolae_Alecu]

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:

[lankmachine]

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.

[BrunPierre]

@lankmachine How did you convert the nulls to values?

[BawejaMedia]

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.