count how often two values ​​occur in one column

[markuskoessler]

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.

[Qrishna]

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 ];

[markuskoessler]

Thanks, but is there any way to do it within the workbook?

[Qrishna]

i have attached the qvw to the above response.

[markuskoessler · Accepted solution]

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)