topic Re: Count, IF and Distinct Query Syntax in QlikView

Count, IF and Distinct Query Syntax

Thu, 01 Oct 2015 13:48:08 GMT

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.

Re: Count, IF and Distinct Query Syntax

MK_QSL — Thu, 01 Oct 2015 13:57:37 GMT

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

Re: Count, IF and Distinct Query Syntax

nigel987 — Thu, 01 Oct 2015 13:59:28 GMT

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.

Re: Count, IF and Distinct Query Syntax

MK_QSL — Thu, 01 Oct 2015 14:01:55 GMT

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