Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
michaelsmith
Contributor III
Contributor III

Data Table Will Not Hide Null Value

I have a data table for which a number of records only show null values.  I've unticked the "Include Null Values" option and typically this would resolve my issue and hide all records with null values.  However, on this occasion, nothing happened and all records remained visible.

The next thing that crossed my mind was that maybe some of the values weren't actually null.  I, therefore, created a copy of the table in question and added the ISNULL() function to all of my expressions.  This confirmed for me that all of the cells that I thought to be null were indeed null.

In an attempt to get around this issue I found a suggestion from the community to create the dimension as a master item using the formula: =IF(AGGR(#EXPRESSION#, #DIMENSION#), #DIMENSION#, null()).  I'm not sure why this worked, but it did and all of the null value records disappeared.  

This being said, it caused another issue.  My initial intent was to use the dimension in question as part of a drill-down functionality.  However, as I'm sure you'll be aware, when using an expression in a drill-down function there is no way of being able to create a dynamic label.  Therefore, when I drill down to the level where this expression is used, the column name becomes "=IF(AGGR(#EXPRESSION#, #DIMENSION#), #DIMENSION#, null())" ie the full expression value.

I, therefore, have two questions:

a) In the first instance, why do the records not disappear when all values are null?

b) Is there any way around the issues being experienced with the drill-down functionality?

As a final point, I should note that each of the cells that are appearing null use some complex set analysis logic such that I can't find a way to get them working with a simple IF statement nor can I easily roll them back to be defined within the script.

Any help would be greatly appreciated.

 

Labels (6)
5 Replies
y_grynechko
Creator III
Creator III

Hey,

did you try to read this:

https://community.qlik.com/t5/QlikView-Documents/NULL-handling-in-QlikView/ta-p/1484472

If you did, are those a null values or missing values? 

 

michaelsmith
Contributor III
Contributor III
Author

Yes, I've read that and yes they are definitely null.

y_grynechko
Creator III
Creator III

Can you share some data? 

And the screen of then you have all null() values and the column is still visible. That sounds weird. 

Sorry to ask if you read and checked, didn't want to try to solve something that was addressed before. 

michaelsmith
Contributor III
Contributor III
Author

Sadly, I'm unable to share the data as it's business sensitive.  However, I can share a simple screen grab with elements redacted:

Build Vs Sales.PNG

The top table shows the result of my expressions in their original form.  For the second table, I have enclosed all of my expressions in an isnull() statement to confirm whether or not cell values are null.  As you can see record E is null in all cells.  In such a case, given that the "Include null values" checkbox has been deselected, I'd expect for record E to have disappeared in the original table, but alas it hasn't.  On the other hand, as all other records have at least one cell populated I'd expect them to remain.

An example of the expression I'm using for the "current stock" column is as follows.

Count({<
                [Date Built]={"<=$(=vMonthEnd)"},
                %CompletionYesNo={'N'},
                [Build Status Code]={'F'},
               Year=,
               Week=,
               [Type]={'PRT'},
               ReservedFlag={'No'}
                >}
               distinct %StockKey)

It's not doing anything overly smart or complicated and I've never come across this issue before.  Any thoughts?

y_grynechko
Creator III
Creator III

I think that there is something wrong with one of the calc.

I loaded this:

Data:
load * inline [
SiteCode, %CompletionYesNo, Build Status Code, StockKey
A, Y,F,1
B,N,G,1
C,Y,F,1
D,N,F,1
E,Y,G,1
];

 

And created pivot:

Capture.PNG

Top columns here are the calculation so there is no way to get null(). Count on null field is going to give 0 and that is expected behavior. Can you share your app without the data? I think there is something wrong with some of the pivot calcs and would like to understand it.