Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I was wondering whether it is possible to test for ‘nothing’ so if the row is missing. For example I have a customer table and an orders table and would like to select all customers that have never ordered anything. I haven’t be able to figure out how it works. My MWE:
//Table containing Nulls (ID=3)
Customer:
LOAD
RowNo() AS ID,
if(RowNo() < 3, Chr(Ord('A') + RowNo() - 1)) AS Name_Null
AUTOGENERATE 3;
//Table containing Nothing (no row with ID=3)
Orders:
LOAD ID,
date(date#(Orderdate, 'DD.MM.YYYY')) as Date_Nothing
INLINE [
ID, Orderdate
1, 10.10.2010
2, 12.12.2012
];
with this code I'm able to catch the 'null Name' (with if or with coalesce) in the customers table, but not the 'nothing' orderdate from the orders table:
The only thing wich would work is a measure in the last column. I know that using the aggregate function I could use it as dimension. But is there an easier way without this workaround and without joining the two tables. I just want to be able to catch and filter for 'nothing' :). QVF is attached
Thanks
Alex
With set analysis, you would typically use e(). However, if you'd like to make a selection, you'll need to either fill in the values, or tell Qlik which field to use for selecting the "nothing". Null values cannot be directly selected.
For example, if you filter the *ID* field by =Count(Orderdate)=0, that should work.
Hi, you can fill null with some generic date like 1/1/9999.
//Table containing Nulls (ID=3)
Customer:
LOAD
RowNo() AS ID,
if(RowNo() < 3, Chr(Ord('A') + RowNo() - 1)) AS Name_Null
AUTOGENERATE 3;
//Table containing Nothing (no row with ID=3)
TMP_Orders:
LOAD ID,
date(date#(Orderdate, 'DD.MM.YYYY')) as Date_Nothing
INLINE [
ID, Orderdate
1, 10.10.2010
2, 12.12.2012
];
right join
LOAD ID
RESIDENT Customer;
Orders:
NoConcatenate
LOAD
ID
, Coalesce(Date_Nothing, MakeDate(9999,1,1)) as Date_Nothing
Resident TMP_Orders;
Drop Table TMP_Orders;
With set analysis, you would typically use e(). However, if you'd like to make a selection, you'll need to either fill in the values, or tell Qlik which field to use for selecting the "nothing". Null values cannot be directly selected.
For example, if you filter the *ID* field by =Count(Orderdate)=0, that should work.
I agree with @Or , for me it works
Hi @Or , thanks a lot, searching IDs with count(...) =0 works fine. Would there be any possibility to kind of convert it to a filter-Box or something like that? The only thing i was able to achieve was by searching in the filter for ID. Which is probably not suitable for End-Users
@FedericoDellAcqua Thanks for your input, i was also thinking about some additional data, to enable this filtering.Maybe not adding pseudo rows in ther orders-table, but adding a column in the custumers table, that indicates customers who have never ordered. But kind of wanted to check if there is an easier way similar to the isnull() function to kind of 'test' for nothing. But probably there is no other workaround for that.
You could set up a button to do this. I haven't attempted to use a filter, though it is probably possible using aggr().