dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Sorry let me be more precise. My previous statement appears to be incorrect.

The situation is as such here.  We have some districts that have
reported nothing for a certain data element and a certain time period.
You appear to be correct, in that zeros are not exported to the
aggregateddatavalue table. In fact, in my case, two districts did not
submit anything at all over the time period of the datamart, and
appear to be completely missing from the aggregateddatavalue table.
This is contrary to my previous mail. I used two simple queries
however to compare between the aggregated indicator and aggregated
data value tables.

This query ..

SELECT COUNT(DISTINCT(organisationunit.name)) from aggregateddatavalue
INNER JOIN organisationunit on aggregateddatavalue.organisationunitid =
organisationunit.organisationunitid
WHERE organisationunit.name ~* 'District'

returns 70. I am missing two districts, who reported nothing. I think
this is the behaviour you a mentioning, namely that zeroes (or blanks)
should not be written to the data mart?

However, for the aggregateindicatorvalue table, blanks are written to
the table as zeroes.

SELECT COUNT(DISTINCT(organisationunit.name)) from aggregateddatavalue
INNER JOIN organisationunit on aggregateddatavalue.organisationunitid =
organisationunit.organisationunitid
WHERE organisationunit.name ~* 'District'

Returns 72 districts now, with the districts that have submitted
nothing, receiving a "0" in the numerator value.

I think somehow this is strange, as the numerator for the indicator
was included as part of the data mart export. So, it does not get
written to the aggregateddatavalue table, but does get written to the
aggregatedindicatorvalue table. I think this may be the issue that Tom
is raising, and the related issue of "blanks" being displayed as zeros
in the maps.

Hopefully, this is a bit clearer and correct this time.

Best regards,
Jason





2009/9/28 Lars Helge Øverland <larshelge@xxxxxxxxx>:
>
>
> 2009/9/26 Jason Pickering <jason.p.pickering@xxxxxxxxx>
>>
>> Hmm, now I am even more confused than normal. We just established that
>> "missing" values in the map, get displayed as 0. If I look in my
>> aggregateddatavalue table, there are values that are exactly displayed
>> as 0. There are other values that get displayed as 0.0 (which appears
>> to be the result of a float operation). If I go back into my dataset,
>> the values that are 0.0 are actual zeros, whereas the 0 (no decimal
>> places) appear to be the result of NULLS (in a recordset) being
>> coerced to zeroes.
>>
>> Now it would seem this is not actually the case, as you say that the
>> datamart does not export empty values?
>>
>> I really need to understand and deal with this, as missing data values
>> is a fact of life in our data here in Zambia.
>>
>
> OK then I must rephrase to "should not" write 0 values to the
> aggregateddatavalue table:-) Not able to reproduce it here but if you and
> Ola are getting 0s after running a datamart a will start looking for a bug.
>
> Lars
>