topic Count total of 1s, 2s, 3s, 4s, and 5s in single expression in QlikView

Count total of 1s, 2s, 3s, 4s, and 5s in single expression

Anonymous — Tue, 15 Dec 2015 17:50:50 GMT

Hello,

I am trying to count survey results to display them in a pie chart.

I want to count the number of 1s, 2s, 3s, 4s, and 5s in the field [Type of PII] in one expression.

I am able to do it in multiple expressions but this will not allow me to show the values on the pie chart.

Please let me know if you know the syntax to make this work in 1 expression.

Thank you!

Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

swuehl — Tue, 15 Dec 2015 18:03:44 GMT

Maybe

=Count({<[Type of PII] = {'1s','2s','3s','4s','5s'}>} [Type of PII])

Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

Colin-Albert — Tue, 15 Dec 2015 18:05:21 GMT

Can you set [Type of PII] as your dimension and then count([Type of PII]) as the expression?

Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

Anonymous — Tue, 15 Dec 2015 18:06:36 GMT

Hi, thank you for the response. This did not work unfortunately.

Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

Anonymous — Tue, 15 Dec 2015 18:08:29 GMT

Worked, thank you!

Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

Anonymous — Tue, 15 Dec 2015 18:11:27 GMT

Is it possible to change the values in the legend. That is, if I want "1" to represent some sort of text?

Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

swuehl — Tue, 15 Dec 2015 18:14:56 GMT

Maybe use a calculated dimension like

=Pick( [Type of PII],

Text1,

Text2,

Text3,

Text4,

Text5

)

Would be even better to map the values in the load script.

Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

Colin-Albert — Tue, 15 Dec 2015 18:16:08 GMT

You can use a dual functionto hold both numeric and text data in the same field.

QlikView Addict: QlikView Functions: dual()

Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

Anonymous — Tue, 15 Dec 2015 18:20:23 GMT

In the script. How would I rename multiple values in a field.

i.e. 1 = General, 2 = private in field 'Type of PII'?

Re: Count total of 1s, 2s, 3s, 4s, and 5s in single expression

swuehl — Tue, 15 Dec 2015 19:24:59 GMT

There are multiple options. You can use a MAPPING table and ApplyMap() function or with few value to map maybe just like

LOAD

[Type of PII],

Dual(Pick( [Type of PII] , 'General','private'), [Type of PII] ) as [Type of PII Text],

...

FROM ...;