Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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)
Sounds like you should be ale to Suppress Nulls in the Dimension setting, hard to tell without an example though.
This one worked:
=Count({<Product= {"=Len(Trim(Product)) > 0"} >}AutoID)
The one I proposed did not work?
You both were right. But now, how can I perform a dashboard for those ones that were excluded?
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.
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?
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
(
You'll get this, and a value to build your exception object(s) from
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.