Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Countif based on another field

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:

Set1Count set1Count set2
A34
B42
C23
D10

Data:                                                                                             

IDN

Set1Set2
1A
2AC
3A
3AB
4B
5BA
6B
7BA
7BC
7B
8C
9CA
9CB
10DA
10DC
1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

See attached qvw


talk is cheap, supply exceeds demand
Not applicable
Author

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)

)

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

Amazing! Thank you!

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