Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Count distinct numbers and excluding records with Set Analysis

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:

SUBJIDENSTATUSTERMINATION_TYPE
X004-001terminatedPremature (screen failure)
X004-002terminatedPremature (screen failure)
X004-003terminatedPremature (screen failure)
X004-004terminatedPremature (screen failure)
X004-005enrolled 
X004-006enrolled 
X004-007terminatedPremature (screen failure)
X004-008terminatedPremature (screen failure)
X004-009terminatedPremature (screen failure)
X004-010terminatedPremature (screen failure)
X009-001enrolled 
X009-002enrolled 
X009-003enrolled 
X009-004terminatedPremature (screen failure)
X009-005enrolled 
X009-006enrolled 
X009-007enrolled 
X009-008enrolled 
X009-009terminatedPremature (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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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)



Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

6 Replies
Highlighted
Master II
Master II

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'}

Highlighted
Partner
Partner

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)



Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Contributor II
Contributor II

=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

Highlighted
Contributor II
Contributor II

@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

Highlighted
MVP
MVP

 

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.   

Highlighted
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes