topic Re: Pivot table - Dynamic values in QlikView

Pivot table - Dynamic values

Thu, 03 Jul 2014 09:46:52 GMT

Hi,

How to display the values dynamically in pivot table based on condition. if value is 0 then 'Yes' and if value is 1 then 'No'.

Please help

Re: Pivot table - Dynamic values

stigchel — Thu, 03 Jul 2014 10:14:34 GMT

You can use an expression to do that

If(Answer=0,'Yes',if(Answer=1,'No','N/A')

But it would be better to do this in load script

MyMap:

Mapping Load * inline [Answer,Label

0,'Yes'

1,'No'

];

Load

Applymap('MyMap',MS1,MS1) as MS1,

Applymap('MyMap',MS2,MS2) as MS2

...

From Your Data

Re: Pivot table - Dynamic values

Thu, 03 Jul 2014 10:47:35 GMT

I already have a expression in Dimensions and Expressions tab

Re: Pivot table - Dynamic values

sasikanth — Thu, 03 Jul 2014 10:51:46 GMT

Here MS1,Ms2,MS3....

these are dimensions or what?

Re: Pivot table - Dynamic values

Thu, 03 Jul 2014 10:53:56 GMT

Hi,

MS1,Ms2,MS3.... are the dimensions (Mystery Shopper)

Re: Pivot table - Dynamic values

sasikanth — Thu, 03 Jul 2014 11:02:30 GMT

Then try this at script level

if(Ms1='0', 'yes',if(Ms1='1','No','N/A)) as Ms1,

if(Ms2='0', 'yes',if(Ms2='1','No','N/A)) as Ms2,

then use in your table

Re: Pivot table - Dynamic values

Thu, 03 Jul 2014 11:12:10 GMT

Hi Sasikanth,

Thanks for your reply.

However i am confused that if i do this is script level then how will i get the percentage values in pivot table.

I am doing the percentage calculation in expression.

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

Thanks,
Selva

Re: Pivot table - Dynamic values

stigchel — Thu, 03 Jul 2014 11:48:12 GMT

For the first part of your if, you don't need a sum (there is but 1 interview, I'm I right ??) and you could do the

if(Value=0,'Yes',if(Value=1,'No'),'N/A')

For the second part you now calculate the % of No answers (if you explained the 0,1 values right) You might want to calculate the Yes answers either in number or text form by doing:

Sum(if(Value=0,1,0)/Count(TOTAL <Question> Value)

Sum(if(Value='Yes',1,0)/Count(TOTAL <Question> Value)

Re: Pivot table - Dynamic values

Thu, 03 Jul 2014 12:48:31 GMT

Hi,

I tried this

if(Value=1,'Yes' , if (Value=0,'No', if(SecondaryDimensionality()=1, sum(Alt(Value,0)), num(sum (Alt(Value,0))/count (MYSTERY_SHOPPER ),'0%') ) ))

I need to display the percentage even if all answers are N/A or No

Please help.

Re: Pivot table - Dynamic values

stigchel — Thu, 03 Jul 2014 15:05:05 GMT

The Dimensionality check still needs to be first:

if(SecondaryDimensionality()=1,if(Value=1,'Yes' , if (Value=0,'No', sum(Alt(Value,0)), num(sum (Alt(Value,0))/count (MYSTERY_SHOPPER ),'0%') ) ))

Re: Pivot table - Dynamic values

Thu, 03 Jul 2014 18:29:48 GMT

I have used this expression

if(SecondaryDimensionality()=1,if(Value=1,'Yes' , if (Value=0,'No', sum(Alt(Value,0)), num(sum (Alt(Value,0))/count (MYSTERY_SHOPPER ),'0%') )

I am getting the below. Pls advice

Re: Pivot table - Dynamic values

stigchel — Mon, 07 Jul 2014 14:10:39 GMT

Check your brackets, if I see it correctly you're missing a closing bracket after

if (Value=0,'No', sum(Alt(Value,0))

so:

if(SecondaryDimensionality()=1,if(Value=1,'Yes' , if (Value=0,'No', sum(Alt(Value,0)))),num(sum(Alt(Value,0))/count (MYSTERY_SHOPPER ),'0%') )