Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Jing
Partner
Partner

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
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
Partner
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)