topic Re: Hide null rows in pivot table / QlikView in QlikView

Hide null rows in pivot table / QlikView

Jing — Sat, 16 Nov 2024 04:09:38 GMT

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

Re: Hide null rows in pivot table / QlikView

GuyIncognito — Fri, 15 Mar 2019 14:46:26 GMT

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!

Re: Hide null rows in pivot table / QlikView

Jing — Fri, 15 Mar 2019 14:49:48 GMT

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

Re: Hide null rows in pivot table / QlikView

GuyIncognito — Fri, 15 Mar 2019 15:44:13 GMT

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