
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
count how often two values occur in one column
Hi there,
I want to count how often it happens that a column contains two specified values per id
Example:
ID | KEY
1 | A
2 | A
2 | B
3 | B
The result should be a formula to count the ID's where KEY = A and KEY = B, for that example it should return 1, because it only happens for one ID that KEY is set to A and B.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your response,
I've found a solution for this which works for me.
I've changed my table (actually it's a database view), now it looks like that:
ID | KEY | KEY_AGG
1 | A | A
2 | A | A,B
2 | B | A,B
3 | B | B
And now I do something like that
count(distinct{<KEYAGG={"A"},KEYAGG={"B"}>}ID)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try using Peek (can use previous() too over here) function to check previous row value in the load script.
data:
load *,
if(RowNo() <> 1
, if(ID = peek(ID) and KEY <> peek(KEY), 1, 0)
, 0) as counter;
load * inline [
ID, KEY
1, A
2, A
2, B
3, B ];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, but is there any way to do it within the workbook?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i have attached the qvw to the above response.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your response,
I've found a solution for this which works for me.
I've changed my table (actually it's a database view), now it looks like that:
ID | KEY | KEY_AGG
1 | A | A
2 | A | A,B
2 | B | A,B
3 | B | B
And now I do something like that
count(distinct{<KEYAGG={"A"},KEYAGG={"B"}>}ID)
