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: 
Jing
Partner - Contributor
Partner - Contributor

Hide null rows in pivot table / QlikView

Hi Qlik Community, 

I want to show in a pivot table the products that are Out of stock at least once on the selected period 

here's an exemple: 

LOAD * Inline [ Prod , status , stock , week
 123, 'Out' , -10 , 201910
 123, 'In'  ,  10  , 201911
 345,'Out'  , -20 , 201910
 345, 'Out', -30 , 201911
 789,'In' , 10 , 201910
  123, 'In' , 10 , 201912
   345, 'In', 30 , 201912
  789,'In' , 10 , 201911
    789,'In' , 10 , 201912
 ];
i want to show in my pivot table only the products 123 and 345 
when i use this expression:
if ( Count(DISTINCT TOTAL<Prod> status) >1     ,sum( stock))
 
it shows 123 and 345 but it shows also 789 with null symbol 
 
Does anyone how to fix it ? 
 
PS: if i check 'Supress Zero Values' it will supress the null values and 0 values also and this is not what i want, i want to see the zeros in my table 
 
Please tell me if you think that you know the solution to this problem 
Thanks for your help
Labels (3)
3 Replies
GuyIncognito
Contributor II
Contributor II

Hi,

If you need to use expressions only and are in a hurry, a clunky way might be to add another expression that acts as a counter:

=if(isnum(if(Count(DISTINCT TOTAL<Prod> status) >1,sum({<status={'Out'}>}stock))),1,0)

Then suppress zeros and it will only show any rows where your original expression results in a number.

I'm sure there is a more elegant way though!

Jing
Partner - Contributor
Partner - Contributor
Author

Hi,
Thank you for your reply
I don't want to supress zeros, i want to see my product when the stock is 0
GuyIncognito
Contributor II
Contributor II

Capture.JPGThe result is this (not great, but I think the better solution lies in scripting)