Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Remove the blank values from a chart

Hi,

I have a chart that will display the products (Dimension) by the count of the IDs (Expression)

But I am getting the products with blank values.

How can I create a dashboard that will exclude the null blank values, I have tried the following:

- Dimension: ='=Product<>'' ''' - Tried with single and double quotes.

- Expression: =Count({< Product = {"<> "" "}>} AutoID) - Tried with single and double quotes

Both syntaxes are correct, but the dashboard doesn't display any data.

Any idea?

1 Solution

Accepted Solutions
Highlighted

Is this a different question then this one? How can filter by Null/Blank/Empty fields

May be try this:

Dimension

Product

Expression:

=Count(AutoID = {"=Len(Trim(Product)) > 0"} AutoID)

View solution in original post

12 Replies
Highlighted

Is this a different question then this one? How can filter by Null/Blank/Empty fields

May be try this:

Dimension

Product

Expression:

=Count(AutoID = {"=Len(Trim(Product)) > 0"} AutoID)

View solution in original post

Highlighted
Contributor III
Contributor III

Sounds like you should be ale to Suppress Nulls in the Dimension setting, hard to tell without an example though.

Highlighted
Creator II
Creator II

This one worked:

=Count({<Product= {"=Len(Trim(Product)) > 0"} >}AutoID)

Highlighted

The one I proposed did not work?

Highlighted
Creator II
Creator II

You both were right. But now, how can I perform a dashboard for those ones that were excluded?

Highlighted
Contributor III
Contributor III

In my experience Qlik does not like Nulls.  Best practice in my opinion is to "create" a value for the Nulls in your load script, likely via a nested If statement, depending on how many values are in your Dimension and if there is a finite list of values.

Highlighted
Creator II
Creator II

Yes, it worked but I must modify it as you posted this:

=Count(AutoID = {"=Len(Trim(Product)) > 0"} AutoID)

The one that worked was this:

=Count({<Product= {"=Len(Trim(Product)) > 0"} >}AutoID)

Now, I need to create a dashboard with the records excluded, is it possible?

Highlighted
Contributor III
Contributor III

Try something like this in your load script:

LOAD
If(xDimension = 'A','A',
If(xDimension = 'B','B',
If(xDimension = 'C','C',
If(xDimension = 'D','D',
'Null')))),
Value
FROM

(
ooxml, embedded labels, table is Sheet1)

You'll get this, and a value to build your exception object(s) from


Highlighted
Creator II
Creator II

it almost worked. The problem is that for blank values QV uses '-', so when I did that all worked except for the blank ones.

I have tried something like this, but for the empty/blank fields QV still putting a '-'

If(fieldname = ' ', 'Test value', fieldname) As Testfield

If(fieldname = NULL, 'Test value', fieldname) As Testfield

If(fieldname = 'NULL', 'Test value', fieldname) As Testfield

none of the above worked.