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

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
AlexanderStraub
Contributor II
Contributor II

Null vs. Nothing as Dimension

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:

 

AlexanderStraub_0-1756895783459.png

 

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

 

Labels (1)
1 Solution

Accepted Solutions
Or
MVP
MVP

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.

View solution in original post

5 Replies
adilio_silva
Contributor III
Contributor III

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;

 

adilio_silva_0-1756900882149.png

 

Or
MVP
MVP

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
Creator II
Creator II

I agree with @Or , for me it works

AlexanderStraub
Contributor II
Contributor II
Author

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

AlexanderStraub_0-1757090726063.png

 

@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
MVP
MVP

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