DistinctCount

I am running into a problem using DistinctCount - I have values in the report
that can either be a valid value, an empty string or null - I believe the
nulls are being discarded in the counts, but is there a way to make sure that
the empty strings are not being counted?This may be out of your jurisdiction, but it sounds like the data needs to be
cleansed? If NULL is valid in the column then you probably shouldn't have
empty string. In any case, if you're using stored procedures, which would be
the recommendation, you can perform some data cleanup there so you're left
with valid values. I'd check with the DBA on why there are both empty
strings and NULLS and use one or the other for absence of "valid data"
"Myles" wrote:
> I am running into a problem using DistinctCount - I have values in the report
> that can either be a valid value, an empty string or null - I believe the
> nulls are being discarded in the counts, but is there a way to make sure that
> the empty strings are not being counted?|||Yes J.P., thank you - you hit the nail on the head - on all accounts.
Unfortunately, I am already 'blue' in the face - but suppose the right thing
to do is hit my head again...I am not sure it is going to change anything,
however, and so am still looking for a way to filter this stuff out of the
counts. Thanks for the reply!
"JP.Sklenka" wrote:
> This may be out of your jurisdiction, but it sounds like the data needs to be
> cleansed? If NULL is valid in the column then you probably shouldn't have
> empty string. In any case, if you're using stored procedures, which would be
> the recommendation, you can perform some data cleanup there so you're left
> with valid values. I'd check with the DBA on why there are both empty
> strings and NULLS and use one or the other for absence of "valid data"
> "Myles" wrote:
> > I am running into a problem using DistinctCount - I have values in the report
> > that can either be a valid value, an empty string or null - I believe the
> > nulls are being discarded in the counts, but is there a way to make sure that
> > the empty strings are not being counted?|||Myles,
Try using the COALESCE(fieldname,0) function in your SQL to change Null
into what is more appropriate, or you could use the CASE WHEN trim(fieldname)
= â'â' THEN null ELSE fieldname END statement to change the empty strings to
nulls.
You could also consider using the filter section of the dataset.
HTH
-walter
"Myles" wrote:
> Yes J.P., thank you - you hit the nail on the head - on all accounts.
> Unfortunately, I am already 'blue' in the face - but suppose the right thing
> to do is hit my head again...I am not sure it is going to change anything,
> however, and so am still looking for a way to filter this stuff out of the
> counts. Thanks for the reply!
>
> "JP.Sklenka" wrote:
> > This may be out of your jurisdiction, but it sounds like the data needs to be
> > cleansed? If NULL is valid in the column then you probably shouldn't have
> > empty string. In any case, if you're using stored procedures, which would be
> > the recommendation, you can perform some data cleanup there so you're left
> > with valid values. I'd check with the DBA on why there are both empty
> > strings and NULLS and use one or the other for absence of "valid data"
> >
> > "Myles" wrote:
> >
> > > I am running into a problem using DistinctCount - I have values in the report
> > > that can either be a valid value, an empty string or null - I believe the
> > > nulls are being discarded in the counts, but is there a way to make sure that
> > > the empty strings are not being counted?