topic Count distinct numbers and excluding records with Set Analysis in QlikView

Count distinct numbers and excluding records with Set Analysis

mlattemann — Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I am still new to the set analysis and this syntax really drives me crazy 😉

from the following table (extract from qvd-file) I want to count all distinct subjids which are no screen failures:

SUBJID	ENSTATUS	TERMINATION_TYPE
X004-001	terminated	Premature (screen failure)
X004-002	terminated	Premature (screen failure)
X004-003	terminated	Premature (screen failure)
X004-004	terminated	Premature (screen failure)
X004-005	enrolled
X004-006	enrolled
X004-007	terminated	Premature (screen failure)
X004-008	terminated	Premature (screen failure)
X004-009	terminated	Premature (screen failure)
X004-010	terminated	Premature (screen failure)
X009-001	enrolled
X009-002	enrolled
X009-003	enrolled
X009-004	terminated	Premature (screen failure)
X009-005	enrolled
X009-006	enrolled
X009-007	enrolled
X009-008	enrolled
X009-009	terminated	Premature (screen failure)

Counting all premature termination works:

Count({<TERMINATION_TYPE = {'Premature (screen failure)'}>} distinct SUBJID)

the other way around I've read would be with -= but this doesn't work and always return 0

Count({<TERMINATION_TYPE -= {'Premature (screen failure)'}>} distinct SUBJID)

even more surprisingly to me counting the non-terminated works as expected:

Count({<ENSTATUS -= {'terminated'}>} distinct SUBJID)

So, why are the excluded TERMINATION_TYPE records are not counted?

Thanks,

Marc

Re: Count distinct numbers and excluding records with Set Analysis

m_woolf — Tue, 31 Mar 2020 13:06:43 GMT

There's probably some fancy syntax you could use to make this work, but I recommend changing your load script so the TERMINATION_TYPE all have values. Something like:

load
If(isnull(TERMINATION_TYPE),''No Failure',TERMINATION_TYPE) as TERMINATION_TYPE,

Then use set analysis to count TERMINATION_TYPE={'No Failure'}

Re: Count distinct numbers and excluding records with Set Analysis

Vegar — Tue, 31 Mar 2020 13:50:14 GMT

It is your NULLs that is causing you trouble with SET analysis.

Try one of these two.

=Count({$< SUBJID = E({<TERMINATION_TYPE ={'Premature (screen failure)'} >}SUBJID)>} distinct SUBJID)

=Count({$-$<TERMINATION_TYPE ={'Premature (screen failure)'}>} distinct SUBJID)

Re: Count distinct numbers and excluding records with Set Analysis

mlattemann — Fri, 03 Apr 2020 12:53:07 GMT

=Count({$< SUBJID = E({<TERMINATION_TYPE ={'Premature (screen failure)'} >}SUBJID)>} distinct SUBJID)

This seems to work. Thanks a lot. "E" stands for Exclude? As I said before, I am really struggling with the syntax of the set analysis...

Thanks,

Marc

Re: Count distinct numbers and excluding records with Set Analysis

mlattemann — Fri, 03 Apr 2020 13:00:55 GMT

@m_woolf:

well, this would be wrong... The termination type is not "No Failure" they are simply not terminated. The source data contains a lot of conditional answers where a lot of values missing, since these depend on other values. (e.g. in the database the question regarding termination type is disabled until the question Termination is not ticked). If I will need to fill all these missing values with some "dummy" values is a lot of work which then also need to be excluded in other counts.

Nevertheless, your proposed solution is working, though.

Thanks,

Marc

Re: Count distinct numbers and excluding records with Set Analysis

tresB — Fri, 03 Apr 2020 13:30:39 GMT

Count({<TERMINATION_TYPE -= {'Premature (screen failure)'}>} distinct SUBJID)

The above doesn't work, because most probably your field's other values are NULLs. And nulls in set analysis is not considered to be part of ALL values or '*'. Here minus operator looks for other values except nulls. Therefore to handle NULL cases like yours, try like:

Count({$ - <TERMINATION_TYPE = {'Premature (screen failure)'}>} distinct SUBJID)

Or, you might want to replace '$' with '1' if you want the result ignorant to selections.

Re: Count distinct numbers and excluding records with Set Analysis

Vegar — Fri, 03 Apr 2020 14:45:00 GMT

Yes. You are selecting the SUBJID that are not associated with the 'Premature...' termination type.