topic Re: Filtering by null in Visualization and Usability

Filtering by null

Thu, 08 Oct 2015 08:07:59 GMT

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).

Re: Filtering by null

Thu, 08 Oct 2015 08:12:11 GMT

oops accidentally clicked assume answered

Re: Filtering by null

igdrazil — Thu, 08 Oct 2015 08:17:52 GMT

Hi,

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

Regards

Chris

Re: Filtering by null

Thu, 08 Oct 2015 08:29:45 GMT

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

Re: Filtering by null

igdrazil — Thu, 08 Oct 2015 08:41:42 GMT

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

Re: Filtering by null

Anonymous — Thu, 08 Oct 2015 08:54:25 GMT

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;

Re: Filtering by null

Thu, 08 Oct 2015 09:05:58 GMT

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.

Re: Filtering by null

Thu, 08 Oct 2015 09:11:37 GMT

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.

Re: Filtering by null

igdrazil — Thu, 08 Oct 2015 09:14:15 GMT

@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

Re: Filtering by null

Thu, 08 Oct 2015 09:21:59 GMT

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

Re: Filtering by null

hic — Thu, 08 Oct 2015 09:33:40 GMT

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:

HIC

Re: Filtering by null

Fri, 09 Oct 2015 03:14:00 GMT

Much thanks Henric, it is solved.

And also Christoph and Kees.