Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Tenuki
Contributor III
Contributor III

Properly handle missing value in dimension

Hello, 

I am trying to create a condition a null field for the field "Key Confirm".

This is my mesure :

=IF(ISNULL(Key_Confirm), 'Not Confirmed', 'Confirmed')
But for null key_confirm, it doesn't work, and only display a empty field with '-'.

What I am missing ? 


Labels (1)
1 Solution

Accepted Solutions
Tenuki
Contributor III
Contributor III
Author

I have try, but it didn't work with my model.

I have found a work around with mapping 

Mymap:

Mapping LOAD 
OrderLineID, 'Confirmed'
FROM Confirmation

Mytable:
OrderLineID, ApplyMap('Mymap', Confirmation_ID, 'Not Confirmed') as Confirmation_Status
FROM OrderLineTable

View solution in original post

4 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi there,

There are two possible reasons for this issue:

1. Depending on the makeup of your chart dimensions, there might be multiple values of Key_Confirm available for a given cell. When multiple values are available, the condition will never be evaluated, and a dash '-' is displayed instead.

2. It's possible that the value is not null, but it's missing, for the combination of the chart Dimension. There is a difference between the NULL value and a missing value. Missing values will not be evaluated as nulls, and they are displayed the same way - with a dash '-'.

Cheers,

Oleg Troyansky

Kaushik2020
Creator III
Creator III

try something like 

=IF(Key_Confirm = '-', 'Not Confirmed', 'Confirmed')

Tenuki
Contributor III
Contributor III
Author

I have try, but it didn't work with my model.

I have found a work around with mapping 

Mymap:

Mapping LOAD 
OrderLineID, 'Confirmed'
FROM Confirmation

Mytable:
OrderLineID, ApplyMap('Mymap', Confirmation_ID, 'Not Confirmed') as Confirmation_Status
FROM OrderLineTable

howdash
Creator II
Creator II

IsNull() function is...tricky. I prefer to use a combination of Len() and Trim() functions instead, like this:

If(Len(Trim(Key_Confirm)) = 0, 'Not Confirmed', 'Confirmed')

This considers both the null and empty cells.

Although, in your case, like @Oleg_Troyansky mentioned, your expression may be evaluating multiple values in a chart which will result in you seeing a '-' instead of either 'Not Confirmed' or 'Confirmed' text.

To handle scenario with an expression generating multiple values for evaluation, I recommend using the Concat() function, like this:

If(Len(Trim(Concat(Key_Confirm))) = 0, 'Not Confirmed', 'Confirmed')