Group By

[garethct89]

Hi all,

I'm trying to count the number of times a consultant (RACF) hits 20 surveys (ConExp) each month. My syntax is below. Key is a concatenate of RACF and Month.

QualifiedConsultant:

left join
load
Key,

if(count(ConExp)>=20, 'Qual','NotQual') as QualCon

Resident MyCustomer
group by Key
;

left join
Load

RACF,

count( distinct QualCon ) as QualVol

Resident MyCustomer

group by RACF;

See example below -

The correct answer should be two because this consultant (RACF) has hit 20 surveys (ConExp) in both months, see below.

The problem seems to be that it is counting each survey as Qualcon.

[Lauri]

Looks like you need to add "where QualCon='Qual' " to your last load statement.