topic Re: Remove the blank values from a chart in QlikView

Remove the blank values from a chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Remove the blank values from a chart

sunny_talwar — Tue, 11 Oct 2016 18:11:51 GMT

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)

Re: Remove the blank values from a chart

jcs_2015 — Tue, 11 Oct 2016 18:21:42 GMT

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

Re: Remove the blank values from a chart

Anonymous — Tue, 11 Oct 2016 18:33:24 GMT

This one worked:

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

Re: Remove the blank values from a chart

sunny_talwar — Tue, 11 Oct 2016 18:35:26 GMT

The one I proposed did not work?

Re: Remove the blank values from a chart

Anonymous — Tue, 11 Oct 2016 18:35:54 GMT

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

Re: Remove the blank values from a chart

jcs_2015 — Tue, 11 Oct 2016 18:43:56 GMT

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.

Re: Remove the blank values from a chart

Anonymous — Tue, 11 Oct 2016 18:49:02 GMT

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?

Re: Remove the blank values from a chart

jcs_2015 — Tue, 11 Oct 2016 19:30:16 GMT

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

Re: Remove the blank values from a chart

Anonymous — Tue, 11 Oct 2016 21:06:39 GMT

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.

Re: Remove the blank values from a chart

jcs_2015 — Tue, 11 Oct 2016 21:27:23 GMT

"-" is the default null in Qlik for an Expression. That can be changed per below. The scripting above was to "display" a null Dimension.

Re: Remove the blank values from a chart

Anonymous — Wed, 12 Oct 2016 13:14:42 GMT

Thank you for the update Jim, I think that now I can change the character there and then set another value to exclude records.

Thank you all for your help.

Re: Remove the blank values from a chart

jcs_2015 — Wed, 12 Oct 2016 14:01:31 GMT

You're welcome!