Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created a pivot table and one of the dimensions is 'Item'. I have checked the 'Suppress when value is null' for this dimension but records are still appearing. How can I get rid of this?? Also, I have item data that contains a + sign in it. I don't want this to show either so how can I get rid of that too????
Here's a screenshot of what it looks like.
You can try If(Len(Trim(Item)) = 0 or Wildmatch(Item,'*+*'),Null(),Item) as an dimension expression. Not sure if it will work or not.
1) That is empty space, not NULL value.
2) Use TEXT() function to avoid your items being interpreted as using scientific notation.
As Lucian says, you have an empty value rather than an actually 'null'.
Have a look at the below for more info on different 'nulls'
http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/30/null-the-invisible-nothing
http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/02/finding-null
http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/28/the-importance-of-nothing
Hope that helps
Joe
I am using Text(Item) as Item during the load
You can suppressed those values in load statement:
Directory;
LOAD Item,
measure
FROM
[157835.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE Item >'
2) You not want this values to be shown or the '+'?
In the script change the Item Field to:
If(Len(Trim(Item)) = 0, Null(),Item) as Item
Could I put an if condition on the item at the dimension level to take care of the blank space??
What about the scientific notation also????
You can try If(Len(Trim(Item)) = 0 or Wildmatch(Item,'*+*'),Null(),Item) as an dimension expression. Not sure if it will work or not.