count distinct failing when reading a QVD

[#1]

Hi, trying to count the distinct days in a months for a position.

when I run this:

CallDays:

LOAD

  [$CRM Position ID],

  MonthStart([CRM Call Date]) as MonthStartDate,

  count(DISTINCT[CRM Call Date]) as UniqueCallDays

  FROM ..\2_BAS_QVD\CRMActual.qvd(QVD)

  group by  [$CRM Position ID]

  ,MonthStart([CRM Call Date])

  ;

it errors:

Error in expression:

')' expected

CallDays:

LOAD

  [$CRM Position ID],

  MonthStart([CRM Call Date]) as MonthStartDate,

  count(DISTINCT[CRM Call Date]) as UniqueCallDays

  FROM ..\2_BAS_QVD\CRMActual.qvd(QVD)

  group by  [$CRM Position ID]

  ,MonthStart([CRM Call Date])

When I remove the DISTINCT, it runs fine (but obviously gives me too high a number of UniqueCallDays)

any idea why the error?

thanks!

[sunny_talwar · Accepted solution]

What if you add a space between DISTINCT and [CRM Call Date]

CallDays:

LOAD [$CRM Position ID],

          MonthStart([CRM Call Date]) as MonthStartDate,

          Count(DISTINCT [CRM Call Date]) as UniqueCallDays

FROM ..\2_BAS_QVD\CRMActual.qvd(QVD)

Group By [$CRM Position ID], MonthStart([CRM Call Date]);

[#3]

Oh my!  that was it. Thanks Sunny!