Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Filtering by null

Dear QLIKies,

How do I filter data by null?

My scenario is as thus, I have 2 tables (2 excel files). Both shared a common field. But I wanna list out those records in tableA that is not found in tableB (missing common field).

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Filtering by null

This is described in the following blog post: Finding NULL

The method is the same in Qlik Sense as in QlikView. However, the "Select Excluded" is found by clicking the search icon, then the three dots:

Sense Select Excluded.png

HIC

11 Replies
Not applicable

Re: Filtering by null

oops accidentally clicked assume answered

igdrazil
Contributor III

Re: Filtering by null

Hi,

do you wanna filter the data in an Expression or during Dataload?

Regards

Chris

Highlighted
Not applicable

Re: Filtering by null

I wanted to filter while viewing the "Sheet". ... Here, I'm trying to look for items that has no "Master Code"

igdrazil
Contributor III

Re: Filtering by null

Do you need the data to be shown or not to be shown?

If second you could SET EXPRESSION out the null fields with something like:

ONLY ( { $ <TableB = { '-= null()' } > } TableA )

not sure if Syntax is correct but i hope you get the Idea behind it

Regards

Chris

501087919
New Contributor III

Re: Filtering by null

This will not work in the frontend. QlikView is not like Excel where you can filter on <Blanks>.

There are several possibilities to solve this issue. During the Load you can use the isnull function like this

if(isnull(<field>,'empty field',<field>) as <field>;

or you create an extra field with filter like this:

if(isnull(<field>,0,1) as <field>filter;

Not applicable

Re: Filtering by null

1) on where should i set this expression?

2) and..."ONLY ( { $ <TableB = { '-= null()' } > } TableA )", on where could I the info on such commands?

Many Thanks Chris.

Not applicable

Re: Filtering by null

I tried. But the  IsNull in Load is not usable in such scenario.

Because, by using ISNULL in the Table with masterCode, there won't be any difference. The reason is that, in the Table with MasterCode, the records is perfect (all with value). But the problem lies in another table having more records than the one table with masterCode.

Creating extra field? Mm...still thinking how will it help and how to do it. Thanks for the reply.

igdrazil
Contributor III

Re: Filtering by null

@Kees: This is Qlik Sense, but i guess you will still be right with this

@Saw: You should check out the Set Analysis Tutorial Videos for this:

Part 1: https://www.youtube.com/watch?v=YMQJnKMkfxg
Part 2: https://www.youtube.com/watch?v=nB-HJ8X1QbI


Regards

Chris

Not applicable

Re: Filtering by null

ok thanks Chris. Would check it out, for now.