Filtering by null

[#1]

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

[#2]

oops accidentally clicked assume answered

[igdrazil]

Hi,

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

Regards

Chris

[#4]

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

[igdrazil]

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

[Anonymous]

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;

[#7]

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.

[#8]

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]

@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

[#10]

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