Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Chiayaochang,
try to use IF(SUM(TIME)=0,0,1). "Show column if" hides column(s) once 0 is returned.
BR
Martin
Hi Martin, i tried your approach but it didn't hide though.
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