Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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
oops accidentally clicked assume answered
Hi,
do you wanna filter the data in an Expression or during Dataload?
Regards
Chris
I wanted to filter while viewing the "Sheet". ... Here, I'm trying to look for items that has no "Master Code"
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
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;
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.
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.
@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
ok thanks Chris. Would check it out, for now.