Conditionally Hide column(s) in a pivot table

[chiayaochang]

Hi, 

If I load a table and create a pivot table in qlik sense, what I want to achieve is to hide column(s) is sum/count value is 0.

table:
LOAD * INLINE [
Factory,Process,Time,
A,QC,1.5
A,Produce,
A,Output,2.1
B,QC,0.5
B,Produce,3.7
B,Output,1.1
C,QC,0
C,Produce,1.7
C,Output,2.2];

However, when i input the expression sum(time) = 0 in  "show column if", it shows an error as following screen shot. Could you please advise how to set the expression correctly? Thanks

[mato32188]

Hi Chiayaochang,

try to use IF(SUM(TIME)=0,0,1). "Show column if" hides column(s) once 0 is returned.

BR

Martin

[chiayaochang]

Hi Martin, i tried your approach but it didn't hide though.

[mato32188]

All right. It seemed to be easy, but I think "Show column if" will not help us.

Let's do it in two steps, first a bit of scripting. Following code will evaluate 0 for process containing 0 value.

table:
load * Inline [
Factory,Process,Time
A,Output,2.2
B,Output,1.3
C,Output,2.5
A,Produce,0.0
B,Produce,3.6
C,Produce,5.9
A,Test,1.1
B,Test,3.2
C,Test,5.9
];

left join
tmptable:
load Evaluate(concat(Time,'*')) as TimeForPivot, Process Resident table Group by Process;

NoConcatenate
final:
load Factory, Process, if(TimeForPivot=0,0,Time) as TimeForPivot, Time
Resident table;

drop table table;
Exit Script;

Secondly,  for your Pivot table object please use sum(TimeForPivot) instead of Time and in "Add-ons" tab uncheck "Include zero values".

It is a kind of workaround.

BR

Martin