
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Filter by Null values
Hi all,
I need to filter a table by Null values (-) in the dimension, please look at the screenshot below.
There are 3 values in WarehouseID, but there are also items with no warehouse defined (last 3 rows).
These Null values cannot be filtered using filter pane or by or by selecting in the table.
In the last Column I tried to change the Null to '00' but it works only as measure, so i can use it in filter pane.
=If(Len(WarehouseID)=0, '00', WarehouseID)
Any formula I defined as a dimension did not replace the nulls.
I need that in the filter pane i have 4 values - 00, 01, 02, 03.
How to solve the problem?
Note - I need to solve it in the UI and not by changing the script.
Thanks.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It is not possible to filter directly on a null in Qlik. You would need to either replace the null values (in script), or you would need to filter on another field based on the requested field being null, e.g. select all inventory rows where the warehouse is null based on the inventory row's ID.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The Data model about how warehouseID is related to other column could help.
Like if in Data Warehouse or Item table has WarehouseId and ItemID
The Your Dimension Expression could be :- aggr(Coalesce(WarehouseID,0),ItemID,x,y,......)
If you only want it for that Table You can stop at ItemID or you can go ahead and add more column to aggregate properly.
Remember this Dimension would show you value of WarehouseID and 0, But after selection it will show the selection pane will show other columns names. (It might solve by creating it as Master Dimension in some versions )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I need to replace the empty cells with '0' or 'blank', something that I can filter with a filter pane.
I tried changing the script (including what was offered) but I still get the empty cells.
I try to solve this in qlik sense desktop and its work, in the client's QS it doesn't.
Other ways to solve this?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this in Filterpane,
=if(isnull(WarehouseID),'00',WarehouseID).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this.
Locate the "WarehouseID" field in the Data Model Viewer. Right-click on "WarehouseID.">"Create..." > "Calculated Dimension."
Below expression
=If(IsNull(WarehouseID), '00', WarehouseID) // This formula checks if "WarehouseID" is null. If it is, it replaces the null with '00'; otherwise, it keeps the original value.
Now, you can use this dimention wherever you want.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What I did from the beginning and everything you suggested work fine.
There was a mistake in the connection between the tables that caused the problem.
Everything works now, thanks and sorry!
