dhis2-users team mailing list archive

[Felix Obareh <fobareh@xxxxxxxxxxxxxx>]

Team,

Thank Jason and Murod i think this will help me a great deal. Jason you got
me me right. Murad your querry is running ok too.

This thread has been very helpful.

Cheers


On Fri, Nov 29, 2013 at 9:13 PM, Murod Latifov <mlatifov@xxxxxxxxx> wrote:

> Hi Felix,
>
> Your sql query should be something like this:
>
> select de.name as dename, ou.name as ouname, pe.startdate,
> dv.dataelementid, categoryoptioncomboid, pe.periodid, value as val,
> dv.sourceid as orgunitid from datavalue dv
> left join dataelement de
> on dv.dataelementid = de.dataelementid
> left join period pe
> on dv.periodid = pe.periodid
> left join organisationunit ou
> on dv.sourceid = ou.organisationunitid
> WHERE de.dataelementid in (2323, 2334, 2335) and dv.periodid=343
>
>
>
>
> On Fri, Nov 29, 2013 at 10:05 PM, Felix Obareh <fobareh@xxxxxxxxxxxxxx>wrote:
>
>> Murod,
>>
>> Ok, let me explain myself...
>> Assume i am collecting data with regard to facility Assessment and my
>> data elements
>>
>> 1) "Lighting and ventilation is observed to be adequate" (YES/NO)
>> 2) "General state of facility" (Option Set) --i) Poor ii) Fair iii)
>> Average
>> 3) "Additional notes related to facility" (string)
>> 4) Does the facility have Handwashing Faility (YES/NO) * This only mean
>> we want to make sure each facility has a handwashing facility
>>
>> This will be done by a District Public Health Officer. You can aggregate
>> this about the facility. I can actually create a form for the DPHO to enter
>> the data for his monthly facility assessment for each facility. The chief
>> DPHO wants a report about each facility. The data is stored into the value
>> table.
>>
>> Value Count is a good idea but i don't want a count but the actual value
>> for analysis
>>
>> Cheers
>>
>> I don't need a count but a
>>
>>
>> On Fri, Nov 29, 2013 at 7:33 PM, Murod Latifov <mlatifov@xxxxxxxxx>wrote:
>>
>>> Hi Felix,
>>>
>>> I do not understand for large on what you mean with "raw data" but can
>>> say if you are not looking for aggregated data for the sake of SQL
>>> requirements, just use count(1 as nothing) or count(value as value)
>>> avoiding type cast issues.
>>>
>>> best,
>>> murod
>>>
>>>
>>> On Fri, Nov 29, 2013 at 9:19 PM, Felix Obareh <fobareh@xxxxxxxxxxxxxx>wrote:
>>>
>>>> Thank you people for you input..
>>>>
>>>> If i can clarify that the query is to pick raw data that otherwise is
>>>> recorded but we cannot retrieve because its not aggregated. This data would
>>>> include the following
>>>> Here is my full query:-
>>>>
>>>> select county.name as county,subcounty.name as subcounty,division.nameas division,
>>>> sublocation.name as sublocation, ou.name as
>>>> facility,triggerstate.value as triggerstate
>>>> from organisationunit ou
>>>> left outer join _orgunitstructure ous
>>>>   on (ou.organisationunitid=ous.organisationunitid)
>>>>   left outer join organisationunit county
>>>>   on (ous.idlevel2=county.organisationunitid)
>>>>   left outer join organisationunit subcounty
>>>>   on (ous.idlevel3=subcounty.organisationunitid)
>>>>   left outer join organisationunit division
>>>>   on (ous.idlevel4= division.organisationunitid)
>>>> left outer join organisationunit location
>>>>   on (ous.idlevel5=location.organisationunitid)
>>>> 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
>>>>
>>>> ========
>>>> ERROR:  invalid input syntax for type double precision: "false"
>>>>
>>>> ********** Error **********
>>>>
>>>> ERROR: invalid input syntax for type double precision: "false"
>>>> SQL state: 22P02
>>>>
>>>> ======
>>>> I want to just get the list of facilities trigger states Trues/False
>>>> ---I dont need aggregated data because the other elements i would want to
>>>> include are date, i have an element with option type (with text) also
>>>> stored into value table. I need to retrieve that and create a report.
>>>>
>>>> I can't use the * select sourceid, sum(cast(value as double
>>>> precision)) as value --- *To get the raw data
>>>>
>>>> A good test is to use the Siera Leone demo and create a query to
>>>> display the facility assessment raw data.
>>>> 1) "General state of facility"
>>>> 2) "Lighting and ventilation is observed to be adequate"
>>>>
>>>> Cheers
>>>>
>>>>
>>>>
>>>> On Fri, Nov 29, 2013 at 6:25 PM, Jason Pickering <
>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>
>>>>> Hi Murod,
>>>>> Just trying to keep it focused on the original topic, but it seems you
>>>>> want to persist.  The initial part of this post was about getting out text
>>>>> data (read the title).  In this case, there is no need for any casting or
>>>>> aggregation. Period.
>>>>>
>>>>> That regex  you post again unfortunately will also not work in many
>>>>> cases , especially if you have integers stored are decimals or negative
>>>>> integers which might be perfectly valid. Please refer to the source code
>>>>> for a more appropriate one. The data which I mention is not "dummy data".
>>>>>  We have lots of problems with this type of data here in Zambia as well as
>>>>> other countries, because most of our data is submitted through mobiles. The
>>>>> trunk mobile clients do not have any validation, so getting data like "09"
>>>>> and "9.0" for an integer is quite common.  We have added some regex
>>>>> validation to the J2ME client to try and prevent most of this junk for
>>>>> getting in, but with the new Light and Smartphone clients, it does not
>>>>> always work. Normally, we run other scripts to flag these values for
>>>>> "follow-up" automatically, to try and resolve what the number is, but it
>>>>> can take time.
>>>>>
>>>>>  In your case, you might think that this regex suits your needs, and
>>>>> would want to reject all decimals and negative numbers. That is up to you.
>>>>> But in our case, we attempt to salvage as much data as possible. Whether
>>>>> that is the right approach or not, well, maybe we can start a new thread
>>>>> for this.  :)
>>>>>
>>>>> Best regards,
>>>>> Jason
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On Fri, Nov 29, 2013 at 5:13 PM, Murod Latifov <mlatifov@xxxxxxxxx>wrote:
>>>>>
>>>>>> 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
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>> --
>>
>