[ETL REPORT] Aggregate report

[sachsk]

Save these reports in the Yenepoya ETL directory Update the query here : https://docs.google.com/spreadsheets/d/15U3mOkcVjHhXlr318uj8DRQNzC6mBOTf-GEPf9SLfTM/edit#gid=693027088

Note : Linelists are also part of this card.

• [x] Antenatal Cases follow up
• [x] Blood pressure measurement in hypertensive
• [x] Children Report
• [x] Child Vaccination Center
• [x] Details of program enrolment
• [x] Environmental Factors
• [x] Health Seeking Behaviour
• [x] Household social class and mother tongue
• [x] Household SocioDemographic Profile
• [x] Individual Education and Occupation
• [x] Individual SocioDemographic Profile
• [x] Influenza Like Illness in last 10 days
• [x] NCD Age Groups
• [x] Postnatal cases - followup
• [x] Self reported morbidity

[Gojo-Taqi]

ETC :- 8 hrs

[Gojo-Taqi]

Updated ETC from now 12:12 pm:- 6 more hours to completed all the reports

[Gojo-Taqi]

All reports have been moved to ETL, it's only that some of the reports have not been tested, hence pushing this card back to support.

[Dinesh2019]

Report Name Issue Cancer warning signs - general Data for linelist is taking time Children Report missing two rows Child Vaccination Center data is missing Details of program enrolment Report showing as 505 Environmental Factors Report is missing

[Dinesh2019]

Getting 504 error for fever report after applying the filter

Private Zenhub Image

[Gojo-Taqi]

The report is trying to access a linelist report, and since the linelist has not yet been moved to ETL, it gives you this error. This issue should be fixed if the linelist card is moved to ETL. Hence pushing this card back to QA ready.

[Dinesh2019]

• [x] Blood pressure measurement in hypertensive (when we click on the line list the report displayed as empty but the aggregate shows the value as 159) we have a value mismatch
• [x] under child report we have Birth order of the child 2-3 count as 206 in aggrgrate but linelist we have 224 data
• [ ] Child Vaccination Center data value and linelist data is mismatch
• [x] Details of program enrolment - HTML response error code: 504, error
• [x] Environmental Factors - Report is missing
• [x] Health seeking behaviour - Linelist - Name as PHC - data mismatch 381 in linelist and in the aggreate we have 325 and we have empty report as well
• [x] Household social class and mother tongue aggregate data is 23 but in the linelist we have 13 data
• [x] Household SocioDemographic Profile under Individuals who are married - count in aggregarte is 2621 but in linelist we have only 105 and also we have data mismatch after applying the filter
• [x] Individual Education and Occupation old query showing error
• [x] Individual socio demographic profile - Linelist - error message (There was an error on the server. Try again or contact site administrators. (Error UID: e3fb50de-2715-4ed8-ad10-bb1175fdd438)
• [x] Influenza Like Illness in last 10 days - data mismatch between aggregate and linelist
• [x] NCD Age Groups data mismatch
• [x] NCD risk factors : Number and Proportion - Individuals with BMI 18.5-22.9 - data mistach
• [x] Self care practices in Diabetes Mellitus - data mismatch
• [x] Self care practices in Hypertension - data mismatch
• [x] Self reported morbidity - data mismatch
• [x] Use of social security schemes - getting error after applying the filter

[vaijayanthisundaram]

For "under child report we have Birth order of the child 2-3 count as 206 in aggrgrate but linelist we have 224 data"- The isnull condition for encounter date time was not Checked in the join condition. Query has been updated and report has been pushed to the server. For "Child Vaccination Center"- ETL migration has not been completed as public views were found in the queries. ETL Query appears to be unchanged from the old query.

[vaijayanthisundaram]

For "Details of program enrolment"- the query was rewritten and pushed to the server. For "Household social class and mother tongue" - the aggregate query was consolidating certain subcategories together irrespective of the main category. Aggregate query has been rewritten and pushed to the server.

[BEULAHEVANJALIN]

NCD risk factors:

• Issue on INNER/LEFT joins
• Issue in Dynamic where clause: use 'between 18.5 and 22.99' instead of using 'between 18.5 and 22.9'

NCD Age Groups:

• Issue in Dynamic where clause. Used something else which is not even mentioned as characteristics

[Gojo-Taqi]

Environmental Factors - this report is giving a few individuals that are null. Need to debug this further.

[Gojo-Taqi]

Individual sociodemograpic report generic queries.

1. select count(*) from individual where subject_type_id = 186;

2. select count(*) from individual where subject_type_id = 186 and gender_id = 229 ;

3. select count(*) from individual where subject_type_id = 186 and gender_id = 230 ;

4. with cte as ( select extract (year from age(i.date_of_birth)) as age, e.observations ->> 'ca9eab9a-da29-4775-b08b-029998c817d0' as obs, row_number() over (partition by e.individual_id order by e.encounter_date_time desc nulls last) as v_no from individual i left join encounter e on e.individual_id = i.id and e.encounter_type_id = 508 where subject_type_id = 186 and i.gender_id = 230 ) select count(*) from cte where v_no = 1 and obs = '9888cdf3-9c0d-4577-8149-aa18f631d821' and age <= 45 ;

5. with cte as ( select extract (year from age(i.date_of_birth)) as age from individual i where subject_type_id = 186 ) select count(*) from cte where age between 0 and 5 ;

6. with cte as ( select extract (year from age(i.date_of_birth)) as age from individual i where subject_type_id = 186 ) select count(*) from cte where age between 10 and 19 ;

7. with cte as ( select extract (year from age(i.date_of_birth)) as age from individual i where subject_type_id = 186 ) select count(*) from cte where age >= 60 ;

8. with cte as ( select extract (year from age(i.date_of_birth)) as age, e.observations ->> 'ca9eab9a-da29-4775-b08b-029998c817d0' as obs, row_number() over (partition by e.individual_id order by e.encounter_date_time desc nulls last) as v_no from individual i left join encounter e on e.individual_id = i.id and e.encounter_type_id = 508 where subject_type_id = 186 and i.gender_id = 230 ) select count(*) from cte where v_no = 1 and obs = '9888cdf3-9c0d-4577-8149-aa18f631d821' and age between 20 and 24 ;

9. with cte as ( select extract (year from age(i.date_of_birth)) as age, e.observations ->> 'ca9eab9a-da29-4775-b08b-029998c817d0' as obs, row_number() over (partition by e.individual_id order by e.encounter_date_time desc nulls last) as v_no from individual i left join encounter e on e.individual_id = i.id and e.encounter_type_id = 508 where subject_type_id = 186 and i.gender_id = 229 ) select count(*) from cte where v_no = 1 and obs = '9888cdf3-9c0d-4577-8149-aa18f631d821' and age between 18 and 24 ;

10. with cte as ( select extract (year from age(i.date_of_birth)) as age from individual i where subject_type_id = 186 and i.gender_id = 229 ) select count(*) from cte where age < 3 ;

11. with cte as ( select extract (year from age(i.date_of_birth)) as age from individual i where subject_type_id = 186 and i.gender_id = 230 ) select count(*) from cte where age < 3 ;

12. with cte as ( select extract (year from age(i.date_of_birth)) as age from individual i where subject_type_id = 186 and i.gender_id = 229 ) select count(*) from cte where age between 3 and 5 ;

13. with cte as ( select extract (year from age(i.date_of_birth)) as age from individual i where subject_type_id = 186 and i.gender_id = 230 ) select count(*) from cte where age between 3 and 5 ;

14. with cte as ( select extract (year from age(i.date_of_birth)) as age, i.gender_id g from individual i where subject_type_id = 186 ) select ( count() filter ( where g = 230 ) 1000 ) / count(*) filter ( where g = 229 ) from cte where age <= 6 ;

15. with cte as ( select extract (year from age(i.date_of_birth)) as age, e.observations ->> 'ca9eab9a-da29-4775-b08b-029998c817d0' as obs, row_number() over (partition by e.individual_id order by e.encounter_date_time desc nulls last) as v_no from individual i left join encounter e on e.individual_id = i.id and e.encounter_type_id = 508 where subject_type_id = 186 and i.gender_id = 230 ) select count(*) from cte where v_no = 1 and obs = '9888cdf3-9c0d-4577-8149-aa18f631d821' and age between 15 and 49 ;

16. with cte as ( select i.observations ->> '49344ffb-d10c-4e30-a284-356396970bcf' obs from individual i where subject_type_id = 184 ) select count(*) from cte where obs = 'ef40f2a9-9a47-4281-a719-aad2cb14e3ca' ;

[Dinesh2019]

• [x] Cancer Warning Signs - Female - Linelist - Column of Name and age is merged cell we have to unmerged the cell and also we have to arrage the column properly
• [x] Children Report - data mismatch between new and old report
• [x] Child Vaccination Center data value and linelist data is mismatch
• [x] Details of program enrolment Linelist
• [x] Environmental Factors aggregrate and linelist data mismatch eg : Households with toilet facility aggregrate shows as 1152 but in linelinst we have 1153
• [x] Household social class and mother tongue - Mother Tongue value is shown as 22 in old report but in new report we have the value as 13,and for social others we have the same scenario,Socio-economic status - Not responsed is present in the old report missing in the new report
• [x] Individual Education and Occupations - When are applying the filter we are getting an error date range from 2021-01-01 and till date,RHDC,someshwara,Kumpala still is this issue exists

[Gojo-Taqi]

• For the report child vaccination report, you will have to verify the data not by the number of rows, but by adding the number of vaccines taken at each vaccination center.
• For the report Details of program enrolment linelist, that whole report needs to be refactored. I have created a new card for that as well.
• For this report "Household social class and mother tongue", the old reports data was incorrect.