dhis2-users team mailing list archive

["Wilson, Randy" <rwilson@xxxxxxx>]

I will check but when i remove the duplicates analytics runs. Also usually
we get many more duplicates 100 or more each time I check.

Randy
On Oct 9, 2015 3:48 PM, "Ant Snyman" <ant@xxxxxxxx> wrote:

> Hi Randy,
> There is actually a constraint on the trackedentitydatavalue table and you
> cannot create two entries with the same programstageinstance and
> dataelementid. I notice that you are grouping on dataelement name, is it
> possible that you could have two dataelements with the same name in your
> dataelement table? I.e different dataelementids but the same name?
> Regards
> Ant
>
> On 9 October 2015 at 10:22, Wilson, Randy <rwilson@xxxxxxx> wrote:
>
>> Hi all,
>>
>> We have an big issue with several of Rwanda's event reporting systems
>> that have been set up using Tracker/Individual records.  For some reason,
>> users are able to create duplicate records in the system through normal
>> data entry.  It seems to happen when they enter a value for an attribute
>> and then a couple of seconds or milliseconds later they change that value.
>> The system then stores both records with last updated several milliseconds
>> apart.
>>
>> This is a big issue because analytics fails to run when it finds these
>> duplicates.
>>
>> Here is the query that finds the duplicates:
>>
>> select programstageinstanceid, dataelementname, count(*) as numrecs from
>> (
>> SELECT
>>   program.name AS programname,
>>   program.description,
>>   programinstance.dateofincident,
>>   programinstance.trackedentityinstanceid,
>>   programinstance.status,
>>   programstageinstance.programstageinstanceid,
>>   programstageinstance.programinstanceid,
>>   programstageinstance.programstageid,
>>   trackedentitydatavalue.value,
>>   dataelement.name AS dataelementname
>> FROM
>>   public.programstageinstance,
>>   public.programinstance,
>>   public.program,
>>   public.trackedentitydatavalue,
>>   public.dataelement
>> WHERE
>>   programstageinstance.programinstanceid =
>> programinstance.programinstanceid AND
>>   program.programid = programinstance.programid AND
>>   trackedentitydatavalue.programstageinstanceid =
>> programstageinstance.programstageinstanceid AND
>>   dataelement.dataelementid = trackedentitydatavalue.dataelementid
>>   ) as foo
>>   group by programstageinstanceid, dataelementname
>>   having count(*) >1
>>   order by numrecs desc;
>>
>> Here is the typical output of the query when there are duplicate records:
>>
>> Programstageinstanceid, dataelementname,numrecs
>> 777921;"TB Sample type_morning Sputum";2
>> 776886;"TB Date of collection_sample";2
>> 773132;"TB History of contact with TPB+ case";2
>>
>> Here is an example of the duplicates we see when we select the duplicated
>> records from the trackedentitydatavalue table.
>>
>> prograstageinstanceid,
>> dataelementid,value,lastupdated,providedelsewhere,user
>> 773132;35105;"true";"2015-10-02 09:32:48.334";FALSE;"abibarimana"
>> 773132;35105;"true";"2015-10-02 09:32:48.508";FALSE;"abibarimana"
>> 776886;31879;"2015-07-17";"2015-10-07 16:25:26.341";FALSE;"mugeni"
>> 776886;31879;"2015-07-14";"2015-10-07 16:25:26.546";FALSE;"mugeni"
>> 777921;35106;"true";"2015-10-08 09:55:36.133";FALSE;"emufasha"
>> 777921;35106;"true";"2015-10-08 09:55:36.278";FALSE;"emufasha"
>>
>> In order to clean up the trackedentitydatavalue table, I need to write
>> these records to a temporary table, delete them
>> from trackedentitydatavalue, then decide which of the duplicate pairs to
>> delete before copying the correct ones back into trackedentitydatavalues.
>>
>> This error seems to happen almost daily when the systems are in full use.
>>
>> Is there a way we can put a constraint on the trackedentitydatavalue
>> table so that it will not accept duplicate values for
>> programstageinstanceid and dataelementid?  I also think there must be an
>> error in the java code that should be updating the first saved record with
>> the one entered by the user later rather than inserting a new record.
>>
>> This is a major bug because it causes analytics to fail and when it fails
>> we cannot run any event reports to get data out of the system.
>>
>> --
>> *Randy Wilson*
>> *Team Leader: **Knowledge Management, Data Use and Research*
>> Rwanda Health System Strengthening Activity
>> Management Sciences for Health
>> Rwanda-Kigali
>> Direct: +250 788308835
>> E-mail: rwilson@xxxxxxx
>> Skype: wilsonrandy_us
>> <http://www.msh.org/>
>> Stronger health systems. Greater health impact.
>> <https://www.facebook.com/ManagementSciencesForHealth>
>> <https://twitter.com/MSHHealthImpact>
>> <https://www.youtube.com/user/MSHHealthImpact>
>> www.msh.org
>>
>> *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-users
>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-users
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
> --
>
> *Ant Snyman*
>
> *Cell: 0824910449*
>
> *Landline: 028 2713242*
>
>
> Health Information Systems Program - SA
>
> *This message and any attachments are subject to a disclaimer published at
> http://www.hisp.org/policies.html#comms_disclaimer
> <http://www.hisp.org/policies.html#comms_disclaimer>.  Please read the
> disclaimer before opening any attachment or taking any other action in
> terms of this electronic transmission.  If you cannot access the
> disclaimer, kindly send an email to disclaimer@xxxxxxxx
> <disclaimer@xxxxxxxx> and a copy will be provided to you. By replying to
> this e-mail or opening any attachment you agree to be bound by the
> provisions of the disclaimer.*
>

-- 
*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.*