I do agree its not the final solution. Just thought I'd share as a useful hack/workaround for some problems. I am no sql wizard, so was quite pleased with myself that this worked :-)
On 24 September 2014 11:26, Rodolfo Melia <[email protected]> wrote: > The problem with that approach is that the function is not available on > Pivot Tables or Event Visualizer, where people need it.. Anyway, nice to > know that you can call it via the SQL views. > > *Rodolfo Meliá* > *Principal | *[email protected] > Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636 > www.knowming.com > > On Wed, Sep 24, 2014 at 11:03 AM, Bob Jolliffe <[email protected]> > wrote: > >> You could install the function into the database through the postgres >> backend. >> >> Then you can include the function into select statements. For example: >> >> select organisationunit.name, getLatestPopulation( >> organisationunit.organisationunitid).value from organisationunit; >> >> On 24 September 2014 10:22, Adebusoye Anifalaje <[email protected]> wrote: >> >>> Hi Bob, >>> >>> I do not suppose you could use this statement in the sqlview since >>> that's limited to Select statements. I recall Jason mentioned that the only >>> way they are able to manage a WASH Program needing just the last current >>> value is to dump the data and post updates though the web api. This seems >>> to be way too much maintenance and would be better if one could use an sql >>> statement like yours instead. >>> >>> Thanks >>> >>> Regards, >>> Busoye >>> On 24 Sep 2014, at 10:16, Bob Jolliffe <[email protected]> wrote: >>> >>> For what its worth the following is the sql I used to extract the latest >>> value of a population dataelement. I am sure it could be generalized and >>> maybe implemented as a getLastValue method on a dataelement. >>> >>> ------------------------------------------------------------------- >>> -- function returns most recent population estimate (and year) for >>> -- an orgunitid >>> ------------------------------------------------------------------- >>> CREATE OR REPLACE FUNCTION getLatestPopulation(orgunitid int, out value, >>> out year) >>> AS $$ >>> BEGIN >>> SELECT DISTINCT ON (sourceid) >>> value as population, >>> extract(year from startdate) >>> INTO >>> value, year >>> FROM datavalue >>> join period on period.periodid=datavalue.periodid >>> join organisationunit on >>> organisationunit.organisationunitid=datavalue.sourceid >>> join dataelement on dataelement.dataelementid=datavalue.dataelementid >>> WHERE >>> organisationunit.organisationunitid = orgunitid AND >>> -- better to use domething like dataelement.code='POP' >>> -- better still to parameterize it >>> dataelement.name ='Population Total' AND >>> extract(year from startdate) <= extract('year' from >>> current_timestamp) >>> ORDER BY sourceid,startdate DESC; >>> END >>> >>> On 24 September 2014 09:55, Rodolfo Melia <[email protected]> wrote: >>> >>>> Hi Lars - I had a quick look in trunk. When editing a Data Element, I >>>> still only see one Aggregation Operator. I was expecting to find two: one >>>> for controlling the operator of aggregations across time, a second selector >>>> for the aggregator across Org Units... >>>> >>>> In terms of priorities, below my list: >>>> 1. Sum + Avg >>>> 2. Count (You could have two type of counts: Different to 0 or Null, 2. >>>> Different to Null >>>> 3. LAST (across time only) >>>> 4. Std Deviation >>>> 5. everything else >>>> >>>> *Rodolfo Meliá* >>>> *Principal | *[email protected] >>>> Skype: rod.melia | +44 777 576 4090 | +1 708 872 7636 >>>> www.knowming.com >>>> >>>> On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland < >>>> [email protected]> wrote: >>>> >>>>> Hi there, >>>>> >>>>> we have implemented support for a "true" average aggregation operator >>>>> in trunk now, which will average across both time and org unit dimensions. >>>>> >>>>> The existing average operator has been renamed to "Average (sum in >>>>> organisation unit hierarchy)" in the UI. >>>>> >>>>> This will be part of 2.17. >>>>> >>>>> regards, >>>>> >>>>> Lars >>>>> >>>>> >>>> >>>> >>>> _______________________________________________ >>>> Mailing list: https://launchpad.net/~dhis2-devs >>>> Post to : [email protected] >>>> Unsubscribe : https://launchpad.net/~dhis2-devs >>>> More help : https://help.launchpad.net/ListHelp >>>> >>>> >>> _______________________________________________ >>> Mailing list: https://launchpad.net/~dhis2-users >>> Post to : [email protected] >>> Unsubscribe : https://launchpad.net/~dhis2-users >>> More help : https://help.launchpad.net/ListHelp >>> >>> >>> >> >
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
