Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
aheavy95
Contributor III
Contributor III

Straight table

Hi all, 

I am trying to create a new report based on an existing report, that will show me all records in which the product Name doesn't exist (null).   To be clear, If there are 4 rows of missing  product Name data in the original report, this report will show only these 4 

In this case, what do I need to add to the product name column and to the rest of the columns?

 

aheavy95_1-1662016199661.png

Thanks, 

Alon

 

Labels (4)
8 Replies
Or
MVP
MVP

The easiest way to achieve this is to remove the other measures and add one new measure - if(isnull([Product Name]),'Missing Product Name').  You would then just need to disable "Include zero values".

 

aheavy95
Contributor III
Contributor III
Author

The thing is I need to show these columns as well 

Or
MVP
MVP

Then you could either modify those columns with a similar if(isnull()) condition, or you could create a new dimension instead:

If(Isnull([Product Name]),'Missing Product')

You would then place this dimension in your chart and uncheck "Include null values" for the dimension.

 

aheavy95
Contributor III
Contributor III
Author

didn't work. it shows me no rows, even though there are such cases

 

BrunPierre
Specialist II
Specialist II

Use below as calculated dimension and uncheck suppress zeroes-values / uncheck 'Include zero values' .

Aggr(IF(Len(trim([Product Name]))=0, 'Missing Product Name'),DocNum)

or

IF(Len([Product Name])<=0,'Missing Product Name')

Or
MVP
MVP

I can confirm that this works, assuming your values are null. I use it regularly.

Or_0-1662028153431.png

And with nulls hidden:

Or_1-1662028182307.png

 

If your values are not actually null, you'll have to check for len(Field)=0, or whatever other option you prefer, rather than isnull().

aheavy95
Contributor III
Contributor III
Author

I've tried both formulas: 

=if(isnull([Company Name]),'missing company')

//=if(len([Company Name])=0,'missing company')

 

And it still doesn't work:

aheavy95_0-1662285342908.png

I know this script should work. Any idea what the problem might be?

 

Or
MVP
MVP

Is the company name actually null? Or is it missing entirely (that is, it's a result of a key to another table and no row exists)? It seems like it might be the latter...

If that's the case, you could force the issue with aggr(), something like:

=if(isnull(aggr(Only(CustomerName),UniqueCustomerID)),'Missing')