Null vs. Nothing as Dimension

[AlexanderStraub]

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

[adilio_silva]

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;

[Or · Accepted solution]

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.

[FedericoDellAcqua]

I agree with @Or , for me it works

[AlexanderStraub]

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.

[Or]

You could set up a button to do this. I haven't attempted to use a filter, though it is probably possible using aggr().