Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbn_timsmith
Contributor
Contributor

How do I filter for a null/empty field in an expression? IsNull doesn't behave as I'd expect.

Hi All,

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
Master III
Master III

May be try like below

Map_Product_class:

Mapping

Load

     'Product Key',

     'Class'

FROM qvd;

[Invoice]:

Load

     'Invoice Number',

     'Product Key'

Applymap('Map_Product_class', 'Product Key','Not Available in Product class') as Product_Key_Class

FROM qvd

View solution in original post

3 Replies
Chanty4u
MVP
MVP

try

if(isnull([Field]) OR [Field]= '','Yes','No')  as field

Chanty4u
MVP
MVP

or

if(len(trim(Field))=0,0,1) as [NewField]


or


If(Len(Trim(field)) > 0, 1, 0) as [NewField]

sasiparupudi1
Master III
Master III

May be try like below

Map_Product_class:

Mapping

Load

     'Product Key',

     'Class'

FROM qvd;

[Invoice]:

Load

     'Invoice Number',

     'Product Key'

Applymap('Map_Product_class', 'Product Key','Not Available in Product class') as Product_Key_Class

FROM qvd