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

[cbn_timsmith]

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

[agigliotti]

maybe try with this:

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

[sasiparupudi1 · Accepted solution]

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;