Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression in pivot table

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.

7 Replies
Anonymous
Not applicable
Author

Hi,

Try below instead,

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

Not applicable
Author

hi,

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

Like this ,

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

Not applicable
Author

try:

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

regards,

MT

Not applicable
Author

Hi,

Still i am facing the issue.

Please find the attached QV file.

Thanks

aveeeeeee7en
Specialist III
Specialist III

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

Not applicable
Author

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.

Not applicable
Author

Hi

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.

filter.png

Thanks