topic Re: Expression in pivot table in QlikView

Expression in pivot table

Mon, 14 Jul 2014 09:02:13 GMT

Hi All,

I am facing issue with null. below is my expression.

if ( sum(Alt(Value,0)) = 1 , Dual('Yes',1) , if ( sum(Alt(Value,0)) = 0 , Dual('No',0) , sum(Alt(Value,0))))

I need to check if the value is null then i have to display as 'N/A'.

Please help.

Thanks.

Re: Expression in pivot table

Anonymous — Mon, 14 Jul 2014 09:07:06 GMT

Hi,

Try below instead,

If(Len(Value)=0, 'N/A', Sum(Value))

Re: Expression in pivot table

Mon, 14 Jul 2014 09:22:01 GMT

hi,

to check value is null or not use isnull() fucntion

Like this ,

if(isnull(Value) , 'N/A', Value)

Re: Expression in pivot table

Mon, 14 Jul 2014 09:31:48 GMT

try:

if(isnull(value)=-1,'N/A',Value)

regards,

Re: Re: Expression in pivot table

Mon, 14 Jul 2014 09:57:24 GMT

Hi,

Still i am facing the issue.

Please find the attached QV file.

Thanks

Re: Expression in pivot table

aveeeeeee7en — Mon, 14 Jul 2014 10:23:59 GMT

Use this Expression:

If(Len(Trim(num(sum ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE1'}>}Alt(Value,0))/
count ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE1'}>} Value ),'0%' )))>0,
num(sum ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE1'}>}Alt(Value,0))/
count ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE1'}>} Value ),'0%' ),'NA')

Kindly see the Attached Qvw.

Regards

Aviral Nag

Re: Expression in pivot table

Mon, 14 Jul 2014 10:31:26 GMT

Write these statements in load script.

NullAsValue Value;

set nullvalue= 'N/A';

load value,

...

....

from datasource;

take value in any where you can get what you want.

hope this helps you.

Re: Re: Expression in pivot table

Mon, 14 Jul 2014 10:53:49 GMT

Hi Aveeeeeee7en

Still i am facing the issue. when the entire row values are null it is not even displaying that row.

For ex: in Q9 - MS3 value is 0 and rest values are null it is display if all the column values(MS1,MS2,MS3,MS4,MS5) are null it is not displaying the entire row.

Thanks