Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Looks like you need to add "where QualCon='Qual' " to your last load statement.