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" <[email protected]> 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 <[email protected]> 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: [email protected] >> 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 : [email protected] >> 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 [email protected] > <[email protected]> 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.*
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-users Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
