topic Re: Re: Countif based on another field in QlikView

Countif based on another field

Wed, 11 Jun 2014 13:26:17 GMT

Hi,

I have data similarly structured to one below. It has IDN (e.g. time stamp) and sets of persons. Same persons appear in both sets but Set1 is assumed to be the complete list of persons. I have table where

Set1 is only dimension
for first column expression is =Count(DISTINCT IDN).
for second column I would like to have something similar as Excel's countif function =countif(column for Set2 in data,set1 value in result table). For first row (Set1 value is A) it should thous count how many times A appears in Set2.

Is this possible?

Result table:

Set1	Count set1	Count set2
A	3	4
B	4	2
C	2	3
D	1	0

Data:

IDN	Set1	Set2
1	A
2	A	C
3	A
3	A	B
4	B
5	B	A
6	B
7	B	A
7	B	C
7	B
8	C
9	C	A
9	C	B
10	D	A
10	D	C

Re: Countif based on another field

Gysbert_Wassenaar — Wed, 11 Jun 2014 13:36:30 GMT

See attached qvw

Re: Countif based on another field

Wed, 11 Jun 2014 13:42:55 GMT

Yes, your solution is an answer to my original question. I should have mentioned that the data was only an example structured similarly as my real data that has approx 400 different values corresponding A,B,C,D.... Thus hard coding each of them is not what I want to do.

pick(match(Set1,'A','B','C','D')

,count({<Set2={'A'}>} total IDN)

,count({<Set2={'B'}>} total IDN)

,count({<Set2={'C'}>} total IDN)

,count({<Set2={'D'}>} total IDN)

)

Re: Re: Countif based on another field

Gysbert_Wassenaar — Wed, 11 Jun 2014 13:57:09 GMT

I've added a second option. See attached qvw.

Re: Re: Countif based on another field

Wed, 11 Jun 2014 14:54:43 GMT

Amazing! Thank you!

I didn't realize that breaking the connection between variables would do the trick.