topic Please help with getting answer from data in QlikView

Please help with getting answer from data

cthomas2 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am curious how I can get an answer based on a group of data and then count the "index" that represents that group... here is an example very simplified table:

Index	Value
1	a
1	a
1	b
2	a
2	a
2	a
3	a
3	b
4	a
5	b

Effectively, I am trying to count the number of indices that distinctly have a value of 'a'. 1 would not be counted because the 3rd record contains a b, 2 would be counted because it only contains the value a (but is only counted once), 3 would not, 4 would count and 5 would not... so when I run the expression on the above table, the resulting count would be 2.

I actually have another level to add to this, but I think if I can understand how to accomplish my goal on just this set of data, I could extract what I need on the larger set...

Any help on this one? Thanks in advance!

Re: Please help with getting answer from data

jwjackso — Mon, 07 May 2018 20:19:12 GMT

Using the AGGR function, if the number of distinct counts is 1, return 1, otherwise 0. Suppress zeroes rows.

Re: Please help with getting answer from data

ehilsinger — Mon, 07 May 2018 20:42:26 GMT

I created a chart with the Index as a dimension and this as the expression.

= Count ({$<[Index] -= P({<[Value] -= {'a'}>})>} Distinct [Value])

Will this get you started?

Re: Please help with getting answer from data

maxgro — Mon, 07 May 2018 20:47:15 GMT

try

- (only(TOTAL <Index> Value) = 'a')

Re: Please help with getting answer from data

cthomas2 — Tue, 08 May 2018 11:17:18 GMT

Thanks for the response, however 3 is incorrect as the distinct values needs to be specifically 'a'.

Re: Please help with getting answer from data

cthomas2 — Tue, 08 May 2018 11:23:07 GMT

This result looks correct, however I just get a memory allocation error when I try putting it in and changing Index & Value for the actual field names...

Re: Please help with getting answer from data

MarcoWedel — Tue, 08 May 2018 21:22:59 GMT

Hi,

another expression might be:

hope this helps

regards

Marco

Re: Please help with getting answer from data

qlikviewwizard — Tue, 08 May 2018 23:17:02 GMT

Try this in a text object.

=count(distinct aggr((only(TOTAL <Index> Value) = 'a'),Index))

Re: Please help with getting answer from data

cthomas2 — Wed, 09 May 2018 11:50:23 GMT

This is it! I was also able to integrate it into the "next level" I indicated into the original post and it matches up with all the counts I've already manually calculated.

It did not occur to me to just try to SUM the -1's, I kept trying to convert them into something else and then count them... such a simple change!

Thanks everyone for trying to help me with this... I learned a lot just from trying everyone's suggestions!