Pivot table issue

Sat, 19 Jul 2014 15:25:20 GMT

Hi All,

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.

Please find the attached my QV file.

Appreciate if some one help me.

Thanks,

Selva

Re: Pivot table issue (My first project )

Sun, 20 Jul 2014 07:38:03 GMT

Hi All,

Finally I find out the solution.

I updated the null value to -1 and filter the values 1 and 0 in expression.

if ( SecondaryDimensionality()=1 ,

if ( sum(Alt(Value,0)) = 1 , Dual('Yes',1) , if ( sum(Alt(Value,0)) = 0 , Dual('No',0), if ( sum(Alt(Value,0)) = -1 , Dual('N/A',0) , sum({<Value={'1','0'}>}[Value])))),

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

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

Thanks.

topic Pivot table issue in QlikView

Pivot table issue

Re: Pivot table issue (My first project )