Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cbn_timsmith
New Contributor

How do I filter for an empty/null value in QlikSense

I have two tables:
[Invoice]:

Load

     'Invoice Number',

     'Product Key'

FROM qvd

and

[Product Table]:

Load

     'Product Key',

     'Class'

FROM qvd

If there are some Products Key values in Invoice that do not have corresponding Product Key rows in Product Table, then Class will display as '-' in a table with fields Invoice Number, and Class.

My end goal is to produce a report that shows me a list of all Product Keys that exist in Invoice, but not in Product Table. I've been trying to go about it with a table that contains Product Key, and Class, and creating a column that is =If(IsNull(Class), 1, 0) expecting a 1 to show if there is no row in Product Table matching the Product Key, however it evaluates to '-' if Class doesn't exist, and 0 if it does, so I'm unable to filter by this value at all.

Is there a way for me to do this? Or, alternatively, a way for me to set a default value on Class in the data load, so that if Class doesn't exist, displays as "Unmapped" or something?

Cheers, Tim

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: How do I filter for an empty/null value in QlikSense

Try Applymap

Map_Product_Table:


Mapping Load


     'Product Key',


     'Class'


FROM qvd

;


[Invoice]:


Load


     'Invoice Number',


     'Product Key',

IF(Applymap('Map_Product_Table','Product Key','NA')='NA',1,0) As MissingKey


FROM qvd;

2 Replies
agigliotti
Honored Contributor II

Re: How do I filter for an empty/null value in QlikSense

maybe try with this:

if( IsNull(Class) or Len(Trim(Class)) = 0, 1, 0)

sasiparupudi1
Honored Contributor III

Re: How do I filter for an empty/null value in QlikSense

Try Applymap

Map_Product_Table:


Mapping Load


     'Product Key',


     'Class'


FROM qvd

;


[Invoice]:


Load


     'Invoice Number',


     'Product Key',

IF(Applymap('Map_Product_Table','Product Key','NA')='NA',1,0) As MissingKey


FROM qvd;

Community Browser