Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

Tags (2)
1 Solution

Accepted Solutions
MK_QSL
Not applicable

Re: Count, IF and Distinct Query Syntax

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

3 Replies
MK_QSL
Not applicable

Re: Count, IF and Distinct Query Syntax

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

nigel987
Not applicable

Re: Count, IF and Distinct Query Syntax

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

Re: Count, IF and Distinct Query Syntax

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))