Output Raw Data in a matrix format -- DHIS2 2.24

Dear Community,

With DHIS2 2.24, I am trying to output raw data in a matrix format. I have used SQL View to output the data. Data values table is presented as following:

orgUnit UID, dataElement1, Value, Period

orgUnit UID, dataElement2, Value, Period

orgUnit UID, dataElement3, Value, Period

orgUnit UID, dataElement4, Value, Period

I have used the following code:

select dv.sourceid as organisationunitid, ou.name, ou.shortname, ou.code as CodeAC, de.name as intitule, pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value from datavalue dv inner join organisationunit ou on (dv.sourceid=ou.organisationunitid) inner join dataelement de on (dv.dataelementid=de.dataelementid) inner join period pe on (pe.startdate=‘2016-02-01’) inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid) where dv.dataelementid in (select dataelementid from datasetmembers where datasetid in (select datasetid from dataset where name=‘dataSetName’));

I would like to output “raw data” for each orgUnit as following:

orgUnit UID, dataElement1, Value, dataElement2, Value, dataElement3, Value, dataElement4, Value, Period

I have used pivot tables , but it is each time doing data agregation of the dataElement.

How should I arrange the SQL code in order to have this result?

Kind Regards,

Tantely.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

Hi Tantely,

Well, you can use a series of union statements to create a column for each of your data elements, but it might get unwieldy.

You can also use the “tablefunc” tools to generate such a cross tab. Its documented here

https://www.postgresql.org/docs/9.5/static/tablefunc.html

Regards,

Jason

···

On Tue, Feb 14, 2017 at 9:37 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear Community,

With DHIS2 2.24, I am trying to output raw data in a matrix format. I have used SQL View to output the data. Data values table is presented as following:

orgUnit UID, dataElement1, Value, Period

orgUnit UID, dataElement2, Value, Period

orgUnit UID, dataElement3, Value, Period

orgUnit UID, dataElement4, Value, Period

I have used the following code:

select dv.sourceid as organisationunitid, ou.name, ou.shortname, ou.code as CodeAC, de.name as intitule, pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value from datavalue dv inner join organisationunit ou on (dv.sourceid=ou.organisationunitid) inner join dataelement de on (dv.dataelementid=de.dataelementid) inner join period pe on (pe.startdate=‘2016-02-01’) inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid) where dv.dataelementid in (select dataelementid from datasetmembers where datasetid in (select datasetid from dataset where name=‘dataSetName’));

I would like to output “raw data” for each orgUnit as following:

orgUnit UID, dataElement1, Value, dataElement2, Value, dataElement3, Value, dataElement4, Value, Period

I have used pivot tables , but it is each time doing data agregation of the dataElement.

How should I arrange the SQL code in order to have this result?

Kind Regards,

Tantely.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049

Dear Jason,

Thank you for this instruction. This is what I am looking for.

Kind Regards,

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.

···

Tantely Raminosoa
Data Officer
Management Sciences for Health
Antananarivo Madagascar
Mobile: 0344280040
E-mail: traminosoa@mikolo.org
Skype: traminosoa

Stronger health systems. Greater health impact.

www.msh.org

On Tue, Feb 14, 2017 at 11:42 AM, Jason Pickering jason.p.pickering@gmail.com wrote:

Hi Tantely,

Well, you can use a series of union statements to create a column for each of your data elements, but it might get unwieldy.

You can also use the “tablefunc” tools to generate such a cross tab. Its documented here

https://www.postgresql.org/docs/9.5/static/tablefunc.html

Regards,

Jason

On Tue, Feb 14, 2017 at 9:37 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Dear Community,

With DHIS2 2.24, I am trying to output raw data in a matrix format. I have used SQL View to output the data. Data values table is presented as following:

orgUnit UID, dataElement1, Value, Period

orgUnit UID, dataElement2, Value, Period

orgUnit UID, dataElement3, Value, Period

orgUnit UID, dataElement4, Value, Period

I have used the following code:

select dv.sourceid as organisationunitid, ou.name, ou.shortname, ou.code as CodeAC, de.name as intitule, pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value from datavalue dv inner join organisationunit ou on (dv.sourceid=ou.organisationunitid) inner join dataelement de on (dv.dataelementid=de.dataelementid) inner join period pe on (pe.startdate=‘2016-02-01’) inner join _categoryoptioncomboname coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid) where dv.dataelementid in (select dataelementid from datasetmembers where datasetid in (select datasetid from dataset where name=‘dataSetName’));

I would like to output “raw data” for each orgUnit as following:

orgUnit UID, dataElement1, Value, dataElement2, Value, dataElement3, Value, dataElement4, Value, Period

I have used pivot tables , but it is each time doing data agregation of the dataElement.

How should I arrange the SQL code in order to have this result?

Kind Regards,

Tantely.

This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049