topic count how often two values occur in one column in QlikView

count how often two values occur in one column

markuskoessler — Tue, 25 Apr 2023 13:10:01 GMT

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.

Re: count how often two values occur in one column

Qrishna — Tue, 25 Apr 2023 13:56:38 GMT

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

Re: count how often two values occur in one column

markuskoessler — Tue, 25 Apr 2023 13:53:41 GMT

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

Re: count how often two values occur in one column

Qrishna — Tue, 25 Apr 2023 13:57:02 GMT

i have attached the qvw to the above response.

Re: count how often two values occur in one column

markuskoessler — Wed, 26 Apr 2023 06:31:16 GMT

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)

topic count how often two values ​​occur in one column in QlikView

count how often two values ​​occur in one column

Re: count how often two values ​​occur in one column

Re: count how often two values ​​occur in one column

Re: count how often two values ​​occur in one column

Re: count how often two values ​​occur in one column

topic count how often two values occur in one column in QlikView

count how often two values occur in one column

Re: count how often two values occur in one column

Re: count how often two values occur in one column

Re: count how often two values occur in one column

Re: count how often two values occur in one column