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: 
Not applicable

Count, IF and Distinct Query Syntax


Need scripting help with an Expression.  I need to count DISTINCT based on two fields, but now I need to add an IF as well.

So, I need to count the number of DISTINCT events (defined by combination of IDKEY and ServiceDate) IF the POTCode is 23.

So far, I have tried numerous combinations.  The following are a couple examples, but none of them work.  Can someone please help me with the syntax?

IF(POTCode = '23', Count(DISTINCT IDKey& ServiceDate))  --- I get no results with this one.

Count({$<POTCode={"23"}>}DISTINCT IDKey & ServiceDate) --The totals are not correct as it is still pulling other POT codes and repeat ServiceDates.

Please help.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Count(Distinct IF(POTCode = '23', IDKey& ServiceDate))

View solution in original post

3 Replies
MK_QSL
MVP
MVP

Count(Distinct IF(POTCode = '23', IDKey& ServiceDate))

nigel987
Creator II
Creator II

Hi,

maybe there is an easier way, but you could create a new field out of IDKEY and ServiceDate and then you could count the distinct values of this field.

KR

MK_QSL
MVP
MVP

Better way is to create a key from combination of IDKey & ServiceDate..

AutoNumber(IDKey& ServiceDate) as Key


Now you can use

Count(Distinct If(POTCode = '23', Key))