dhis2-users team mailing list archive

[Murod Latifov <mlatifov@xxxxxxxxx>]

Hi Jason,

I thought my points were of some help to solve the issue and discuss those
appearing from initial issue. What you thought about first call is not
right and what I proposed was a solution. After you added dummy data being
stored, where new topic was introduced (off initial topic). And now you are
saying this kind of bureaucratic on topic/off topic things.
With regards to regexp you are not bound with what I demonstrated, you can
with the same ease, please use " and dv.value~E'^[1-9][0-9]?$'" instead if
the case is so worth, no probs.

best,
murod


On Fri, Nov 29, 2013 at 7:58 PM, Jason Pickering <
jason.p.pickering@xxxxxxxxx> wrote:

> Hi Murod,
>
> I do not think anyone is happy about it, and thus my blueprint to provide
> server-side regex validation of data, which has yet to be implemented.
> However, the fact of the matter is, it can happen now, and having some data
> is usually better than having no data at all.  That regex which you mention
> there will also not work in all cases, because things like "00" are quite
> common and cannot be cast, thus the need for the "heavier" regex which is
> used by the analytics process. Such values would pass the regex because it
> is all digits, but will fail the cast.  You may not be happy about it, but
> it is usually necessary to be sure the values which should be numeric, can
> actually be cast to an integer.
>
> Again, seemingly off-topic from the original post?
>
> Regards,
> Jason
>
>
>
>
>
> On Fri, Nov 29, 2013 at 4:51 PM, Murod Latifov <mlatifov@xxxxxxxxx> wrote:
>
>> Hi Jason,
>>
>> By telling these you mean data stored in DHIS2 at some implementations is
>> not trusted, that is not cleaned enough. How can one use these data for
>> analysis? Does it bring TRUE result for decision making? I don't think such
>> data should exist. At least some Data management action could clean data
>> against data element declarations and value stored. After, there shouldn't
>> be such worries.
>> You can use regexp within SQL command to:
>> select dv.dataelementid, categoryoptioncomboid, periodid, sum(cast (value
>> as int)) as val, dv.sourceid as orgunit, aggregationtype from datavalue dv
>>  left join dataelement de
>> on dv.dataelementid = de.dataelementid
>> WHERE aggregationtype = 'sum' and domaintype='aggregate' and
>> dv.value~E'^\\d+$'
>> group by dv.dataelementid, dv.sourceid, categoryoptioncomboid, periodid,
>> aggregationtype
>>
>> Please note this addition  and dv.value~E'^\\d+$', which makes SQL run
>> failure safe.
>> But in overall am not happy with such data being stored without cleansing
>> after import.
>>
>> best,
>> murod
>>
>>
>> On Fri, Nov 29, 2013 at 7:10 PM, Jason Pickering <
>> jason.p.pickering@xxxxxxxxx> wrote:
>>
>>> Hi Murod,
>>> You would think this would work, and it might for you. The problem is
>>> there is no way to "trust" that the raw data values can be cast. We have
>>> had a lot of problems with this, but mostly because of 1) Legacy data
>>> before the different value types were implemented and 2) data which is
>>> received from mobile clients, which do not have the same restrictions as
>>> from the front-end. So, by relying on the aggregationtype, you must really
>>> know your data is clean, otherwise, it only takes one bad value for the
>>> aggregation with SQL not to work. You can see the way the analytics gets
>>> around this, by applying a regex. Whether it is "heavy" or not, I guess
>>> really depends on your reference frame.
>>>
>>> However, getting back to the top of the thread, I thought Felix's
>>> original issue was not being able to data which was stored as a date or
>>> text. My original point was that the casting and summing of this type of
>>> data is simply not possible with these simple SQL statements. But maybe I
>>> misunderstood Felx's original issue.
>>>
>>> Regards,
>>> Jason
>>>
>>>
>>>
>>> On Fri, Nov 29, 2013 at 3:15 PM, Murod Latifov <mlatifov@xxxxxxxxx>wrote:
>>>
>>>> Hi Jason,
>>>>
>>>> Yes, it holds true in case if raw data being called. But query was with
>>>> aggregation elements. Technique I proposed will exactly remove none
>>>> convertible values from the query, thus there won't be cast exception. No
>>>> need to deploy heavy regex or stored procedure here, this is doable. Sample
>>>> code here:
>>>>
>>>> select dv.dataelementid, categoryoptioncomboid, periodid, sum(cast
>>>> (value as int)) as val, dv.sourceid as orgunit, aggregationtype from
>>>> datavalue dv
>>>> left join dataelement de
>>>> on dv.dataelementid = de.dataelementid
>>>> WHERE aggregationtype = 'sum' and domaintype='aggregate'
>>>> group by dv.dataelementid, dv.sourceid, categoryoptioncomboid,
>>>> periodid, aggregationtype
>>>>
>>>> This should give an idea on how to manage avoiding these exceptions
>>>> just with plain SQL.
>>>>
>>>> regards,
>>>> murod
>>>>
>>>>
>>>> On Fri, Nov 29, 2013 at 5:46 PM, Jason Pickering <
>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>
>>>>> Well, it is very hard to see what is going on without the entire
>>>>> query. My point here is there is no way to aggregate text, without some
>>>>> sort of procedure, so you cannot cast anything, nor can you sum it, because
>>>>> in the datavalue table, everything is stored as text. Just like with the
>>>>> analytics, you must first filter out all values which you think can be cast
>>>>> to a double with a regex, and then perform the sum/average/other
>>>>> aggregation operator.  If you just want to get the raw data values back,
>>>>> then there should be no aggregation anyway, thus, no need for the sum
>>>>> operator.
>>>>>
>>>>> Regards,
>>>>> Jason
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Fri, Nov 29, 2013 at 2:39 PM, Murod Latifov <mlatifov@xxxxxxxxx>wrote:
>>>>>
>>>>>> I am afraid Jason, it won't work that way. In Postgres aggregating
>>>>>> (SUM, AVG) for varchar will give exception. There is no onbuilt type
>>>>>> casting at this point.
>>>>>>
>>>>>>
>>>>>> On Fri, Nov 29, 2013 at 5:09 PM, Jason Pickering <
>>>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>>>
>>>>>>> You cannot cast text to a double so just remove that cast operator
>>>>>>> and you should be fine.
>>>>>>>
>>>>>>> --Sent from my mobile
>>>>>>> On Nov 29, 2013 1:16 PM, "Felix Obareh" <fobareh@xxxxxxxxxxxxxx>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Thank Jason,
>>>>>>>> I have decided to go with the SQL Query. I am using the SQL Query
>>>>>>>> example i the user manual together with the demo database for Sierra Leone.
>>>>>>>>
>>>>>>>> It is working fine for elements of integer type and i can run the
>>>>>>>> query from my pgAdmin and i get them fine. I have a problem with displaying
>>>>>>>> data elements of text and varchar type.
>>>>>>>>
>>>>>>>> Here is what am running as querry:-
>>>>>>>>
>>>>>>>> ------Trancated--------
>>>>>>>>
>>>>>>>> left outer join organisationunit Sublocation
>>>>>>>>   on (ous.idlevel6=sublocation.organisationunitid)
>>>>>>>> left outer join (
>>>>>>>>   *select sourceid, sum(cast(value as double precision)) as value*
>>>>>>>>   from datavalue
>>>>>>>>   where dataelementid=8305
>>>>>>>>
>>>>>>>>  group by sourceid) as *triggerstate* on
>>>>>>>> triggerstate.sourceid=ou.organisationunitid
>>>>>>>> where ous.level=7
>>>>>>>>
>>>>>>>> ****If i run the same query for a data element of date of text type
>>>>>>>> or yes/no
>>>>>>>> ***NB the triggerstate store a true or false value in the the
>>>>>>>> datavalue table
>>>>>>>>
>>>>>>>> *HERE is the SQL error i get*
>>>>>>>>
>>>>>>>> ERROR:  invalid input syntax for type double precision: "false"
>>>>>>>>
>>>>>>>> ********** Error **********
>>>>>>>>
>>>>>>>> ERROR: invalid input syntax for type double precision: "false"
>>>>>>>> SQL state: 22P02
>>>>>>>>
>>>>>>>>
>>>>>>>> ----------------------------------
>>>>>>>> Please assist
>>>>>>>>
>>>>>>>>
>>>>>>>> On Wed, Nov 27, 2013 at 5:44 PM, Jason Pickering <
>>>>>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>>>>>
>>>>>>>>> Hi Obare,
>>>>>>>>> The reason for this is because you will only be able to see the
>>>>>>>>> aggregated data in DHIS2. In your case, it does not seem you need the
>>>>>>>>> aggregated data, but instead the raw data. One way to do this is with a
>>>>>>>>> custom report, which would use a JDBC data source, and then an SQL query to
>>>>>>>>> retreive the data. You might need some paramaters like the period and the
>>>>>>>>> particular orgunit. You can read about custom reports in the DHIS2
>>>>>>>>> documentation. Another possible solution would be an SQL Query, which would
>>>>>>>>> allow you to pull out the raw data into a CSV file for subsequent analysis.
>>>>>>>>>
>>>>>>>>> Hope this helps.
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>> Jason
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Wed, Nov 27, 2013 at 2:34 PM, Felix Obareh <
>>>>>>>>> fobareh@xxxxxxxxxxxxxx> wrote:
>>>>>>>>>
>>>>>>>>>> Hi,
>>>>>>>>>>
>>>>>>>>>> I a form similar to the Facility Assessment Form in the
>>>>>>>>>> demo.dhis2.org . I have done some data entry but i don't know
>>>>>>>>>> how to get the report for that form
>>>>>>>>>>
>>>>>>>>>> Among my Elements are:-
>>>>>>>>>>
>>>>>>>>>> 1) Date Facility was commissioned
>>>>>>>>>> 2) Facility state on Commissioning with options set of ( Ready,
>>>>>>>>>> Lacking Some Departments, Not ready)
>>>>>>>>>> 3) Date of Certification
>>>>>>>>>> 4) Date of Verification
>>>>>>>>>> 5) Source of Funds with option sets (Government, Donor)
>>>>>>>>>> 6) Is facility well maintained (Yes/No)
>>>>>>>>>> 7) Key issues identified & suggested solutions (Text)
>>>>>>>>>> 8) Number of Practitioners in the Facility
>>>>>>>>>>
>>>>>>>>>> I can the above data on the entry form by i cant create a single
>>>>>>>>>> report about the facility. The pivot tables on show the value of element 8.
>>>>>>>>>> The value of the number of practitioners.
>>>>>>>>>>
>>>>>>>>>> Any assistance will be highly appreciated. I have three more
>>>>>>>>>> Facility based forms which i want to get reports for across the country
>>>>>>>>>>
>>>>>>>>>> Cheers
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>>
>>>>>>>>>> _______________________________________________
>>>>>>>>>> Mailing list: https://launchpad.net/~dhis2-users
>>>>>>>>>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>>>>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>>>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> Mailing list: https://launchpad.net/~dhis2-users
>>>>>>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>