topic Re: Count function in variable in QlikView

Count function in variable

infock12 — Fri, 10 Jul 2015 08:53:33 GMT

Hello all,

Happy Friday! Could someone please explain how can I use Count function in a variable. For example, I want to count the number of questions where Answer=Yes + Answer=No/Total Answer in percentage. Something like below.

vTotal=Count(Answer=Yes+No)/Count(Answers), #.##%

I tried below in the load script but it may not have worked because with

SET vTotal=count({<Answer={'Yes', 'No'}> Answers}/count(Answer)

It didn't work...perhaps set analysis syntax cannot be used in load script.

Any help is appreciated.

Re: Count function in variable

Gysbert_Wassenaar — Fri, 10 Jul 2015 08:55:43 GMT

That's right, set analysis cannot be used in the script. But creating a variable with a set analysis expression will work fine. But make sure to use the exact case sensitive field names. Answers and Answer are two different field names. Perhaps you simply made a typo:

SET vTotal=count({<Answer={'Yes', 'No'}> Answers}/count(Answer)

Re: Count function in variable

alexandros17 — Fri, 10 Jul 2015 09:01:19 GMT

Take a look at my example

Re: Count function in variable

Fri, 10 Jul 2015 09:03:15 GMT

Hi,

You can try this at script lever as

Table1:

load count(if(Answer='YES'or Answer='NO',1,0))/Count(Answer) as x

from xxx

group by Answer;

vTotal=peek('x',-1,'Table1');

Re: Count function in variable

infock12 — Fri, 10 Jul 2015 10:26:01 GMT

Thanks Gysbert! Yes, it is a typo.

Re: Count function in variable

infock12 — Fri, 10 Jul 2015 10:28:53 GMT

Thanks Reshma! Very useful.