Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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 )
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?
Try this in Filterpane,
=if(isnull(WarehouseID),'00',WarehouseID).
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.
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!