Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jisephcirspy
Contributor III
Contributor III

count, distinct, if

So I want to count the entries where the state is 'CRIT', but distinct the entries with the same names.
It is a bit like this:

count(DISTINCT[name]) but with the condition If(state='CRIT', 1, 0))

 

 

If(state='CRIT', 1, 0))

 

 

 

count(DISTINCT[name]) 

 

 

Labels (3)
2 Solutions

Accepted Solutions
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @jisephcirspy 

You have all the bits you need there, just need to put them together.

The thing you can use to your advantage is that the COUNT function (DISTINCT or otherwise) does not count NULLs, so you just have to put a null in where it is not the type you wish to count:

count(DISTINCT If(service_state='CRIT', alias, null()))

 

That should do the trick.

Steve

View solution in original post

dieterwoestemeier
Contributor II
Contributor II

Try to use set analysis where possible because it performs way better than IFs.

In your case:

count({<state={'CRIT'}>} DISTINCT([name]))

View solution in original post

4 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @jisephcirspy 

You have all the bits you need there, just need to put them together.

The thing you can use to your advantage is that the COUNT function (DISTINCT or otherwise) does not count NULLs, so you just have to put a null in where it is not the type you wish to count:

count(DISTINCT If(service_state='CRIT', alias, null()))

 

That should do the trick.

Steve

jisephcirspy
Contributor III
Contributor III
Author

Thank you very much @stevedark !!! helped me a lot

dieterwoestemeier
Contributor II
Contributor II

Try to use set analysis where possible because it performs way better than IFs.

In your case:

count({<state={'CRIT'}>} DISTINCT([name]))

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @dieterwoestemeier 

D'oh! Focussed on answering the question in hand that I missed that there was a much better way of achieving the same thing.

Thanks for posting a better way.