topic Re: Display Null values in QlikView

Display Null values

Wed, 16 Jul 2014 10:57:03 GMT

Hi All,

I am trying to display the values in pivot table. it is not displaying the rows with value null.

As per below is the script for Q4 column the values are empty. Pivot table is not displaying Q4.

I need to display even though it is null

Script:

CrossTable(Question,Value,3)

LOAD MS, DELAER, COUNTRY, Q1, Q2, Q3, Q4 INLINE [

Q1, Q2, Q3, Q4, MS, DELAER, COUNTRY

1, 1, 1, , MS1, ,

0, 0, , , MS2, ,

1, 1, 1,, MS3];

Pivot table

Expression

if(SecondaryDimensionality()=1, sum(Value), num(sum(Value)/count(Value),'0%') )

Thanks,

Selva

Re: Display Null values

parthiband — Wed, 16 Jul 2014 11:04:22 GMT

You want to display the Q4 rite?

Just Goto Properties-> Presentation -> Uncheck the supress zero values

Thanks

Re: Display Null values

Wed, 16 Jul 2014 11:21:54 GMT

Hi Parthipan,

Thanks for your efforts.

In the expression i am trying to display 'Yes' if value=1 and 'No' if value=0 and 'N/A' if value=null

if ( SecondaryDimensionality()=1 ,

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

num(sum ({<Question={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'}>}Alt(Value,0))/

count ({<Question={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'}>} Value ),'0%' ))

then is is not displaying rows with null or empty.

how to check for null value in expression, i tried isnull and len(value)=0. No luck

Thanks,

Selva

Re: Display Null values

parthiband — Thu, 17 Jul 2014 02:15:57 GMT

Selva,

We usually use

if( Len(Trim(Field))=0, 'Null()',1)

to find out the null values.

Trim() is always important in finding the null or empty values.

Please let me know, if it works.

Thanks,

Re: Re: Display Null values

Sat, 19 Jul 2014 15:31:42 GMT

Hi Parthiban,

I am using the below expression

if ( SecondaryDimensionality()=1 ,

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

num(sum ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE2'}>}Alt(Value,0))/

count ({<Questions={'Q1','Q2','Q3','Q4','Q5','Q6','Q7A','Q7B','Q8','Q9','Q10','Q11','Q12','Q13'},WAVE={'WAVE2'}>} Value ),'0%' ))

i have values 1,0,null in my DB for columns Q1M,Q2,Q3...Q13. I need to display if value =1 then Yes, if value =0 then No and if value=null then N/A.

I cannot do this in script since calculation is involved.

Below is my script:

Crosstable(Questions, Value,4)

SQL Select MYSTERY_SHOPPER, DEALER, COUNTRY, WAVE, Q1, Q2, Q3, Q4, Q5,Q6,Q7A,Q7B,Q8,Q9,Q10,Q11,Q12,Q13,

Q14,Q15A,Q15B,Q15C,Q15D,Q15F,Q15G,Q15H,Q15I,Q16,Q17,Q18,Q19,Q20,Q21,

Q22,Q23,Q24,Q25,Q26,Q27,Q28,Q29,Q30,Q31,Q32_A ,Q32_B,Q32_C,Q32_D,Q33,Q34,Q35,Q36,Q37,Q38,Q39,Q40,Q41,Q42,Q43,Q44,Q45,

Q46,Q47,Q48,Q49,Q50,Q51,Q52,Q53,Q54

From XX_JLR_SALES_DATA_ENTRY_NEW WHERE ENABLED='YES';

Pivot table Dimension :

Questions

Mystery_Shopper

In Chart properties -> Presentation -> Uncheck "Supress Zero-Values"

For example for Q9,

Mystery_Shopper1=1

Mystery_Shopper1=0

Mystery_Shopper1=Null

Then pivot table display

Yes, No, N/A

Mystery_Shopper1=Null

As above if all the values are null, Then pivot table is not displaying Q9.

This is the issue i am facing.

Thanks,

Selva