v 2023.11.0

This release represents the ETL scripts used to run the refresh on Nov 17, 2023

What's Changed

• Feature/adding dimensional layer scripts by @nobert-mumo in #2
• Feature/re arranging the scripts by @nobert-mumo in #3
• added keys to Dim tables by @cbrianbet in #5
• Feature/working on more dimensions by @nobert-mumo in #6
• Feature/ Adding New dims, yaaay! by @nobert-mumo in #7
• Changes to dim facility by @Marymary-dev in #8
• Addition dim treatment type by @Marymary-dev in #10
• Load intermediate tables by @Marymary-dev in #11
• Changes to remove Schema in intermediate tables by @Marymary-dev in #13
• Adding new intermediate tables for use in creating downstream models by @nobert-mumo in #12
• Load intermediate tables by @Marymary-dev in #15
• changes to PregnancyAtARTInitiation by @Marymary-dev in #16
• Adding new fact tables, intermediate table and other small changes by @nobert-mumo in #17
• Feature/Adding viral load fact table by @nobert-mumo in #21
• Additions to PregnancyAsAt intermediate table by @Marymary-dev in #14
• Load fact tables by @Marymary-dev in #19
• Dev by @Marymary-dev in #23
• Load fact tables by @Marymary-dev in #25
• Dev by @Marymary-dev in #26
• Feat/intermediate tables improvements by @nthusi-codes in #28
• Create load_FACTPatientExits by @Marymary-dev in #24
• Additions to FactTables FactMANifest by @Marymary-dev in #27
• adding new tables for IPT & CD4 by @nobert-mumo in #29
• Feature/Adding art outomes to DimPatient and small tidying up by @nobert-mumo in #30
• Create load_Linelist-FACTART by @Marymary-dev in #33
• Update load_intermediate_LastPatientEncounter by @Marymary-dev in #34
• Update load_FactART.sql by @Marymary-dev in #37
• Aggregate tables for OTZ outcomes and cohort retention by @cbrianbet in #38
• Feat/aggregate otz outcomes tables by @cbrianbet in #42
• Feat/aggregate time to art report tables by @nthusi-codes in #35
• Change reporting folder by @cbrianbet in #43
• Add AgeLastVisit column to FactArt by @nthusi-codes in #44
• Feat/aggregate otz table by @cbrianbet in #46
• Feat/aggregate viral load report tables by @nthusi-codes in #41
• Fix/fact tables merge by @nthusi-codes in #51
• Added covid aggregate table by @cbrianbet in #40
• Feat/aggregate otz table by @cbrianbet in #54
• Feature/adding aggregate TXCur table by @nobert-mumo in #49
• Dev by @Marymary-dev in #55
• Load aggregate tables by @Marymary-dev in #52
• Adding IsTXCurr in the linelist by @Marymary-dev in #50
• Dev by @Marymary-dev in #56
• Feat/aggregate dsd table by @cbrianbet in #47
• Create load_aggregate_otz.sql by @cbrianbet in #39
• Load aggregate tables by @Marymary-dev in #57
• Feat/ovc reports by @nthusi-codes in #48
• Feature/building a Fact Adverse events table by @nobert-mumo in #53
• Load aggregate tables by @Marymary-dev in #59
• Fix/report scripts cleanup by @nthusi-codes in #62
• changes to Intermediate_ARTOutcome by @Marymary-dev in #63
• Changes to ARTOutcome logic by @Marymary-dev in #64
• Load intermediate art outcome by @Marymary-dev in #65
• Changes to LastPatientencounter by @Marymary-dev in #66
• Someone deleted this linelist by @cbrianbet in #67
• Create load_aggregate_nupi.sql by @cbrianbet in #69
• changes to effective discontinuation date by @Marymary-dev in #70
• Changes to include correctly patients with TCAs >365 days by @Marymary-dev in #71
• Fixed column reference errors by @cbrianbet in #68
• Feature/Enhancing-DimPatient-to-accomodate-HTS-clients by @nobert-mumo in #61
• Load intermediate art outcome by @Marymary-dev in #73
• changes to Last encounter by @Marymary-dev in #76
• Last patient encounter by @Marymary-dev in #78
• Added TxCurr Column by @cbrianbet in #80
• Update load_aggregate_dsd.sql by @cbrianbet in #81
• Capping in all CTE for LE by @Marymary-dev in #82
• Add aggregate adverse events report by @nthusi-codes in #60
• Correct table names used by @cbrianbet in #84
• Feature/Modelling-HTS-Client-Tests-Fact-table by @nobert-mumo in #74
• Feature/Modelling-Client-Linkages-fact-table by @nobert-mumo in #75
• Feature/Modelling-Fact-HTS-Partner-Tracings by @nobert-mumo in #79
• Feature/Modelling-Fact-HTS-Test-Kits-table by @nobert-mumo in #85
• Added TXCurr check for Otz tables also added eligible +enrolled aggregate by @cbrianbet in #72
• Feature/Modelling-Fact-HTS-ClientTracing by @nobert-mumo in #77
• Load intermediate art outcome by @Marymary-dev in #86
• Minor changes to explicitly call variables by @Marymary-dev in #87
• Sequencing the cases by @Marymary-dev in #88
• Explicitly used column names to fix arising errors by @cbrianbet in #90
• Feat/aggregate time to art report tables by @nthusi-codes in #91
• Update load_linelist_covid.sql by @cbrianbet in #92
• Update load_linelist_covid.sql by @cbrianbet in #93
• Merge to production by @nthusi-codes in #96
• Optimize_StartRegimens_aggregate by @Marymary-dev in #58
• Added missing columns in art linelist also added AdverseClientsCount to Agg Adverse events by @cbrianbet in #94
• Rename CTPartner,CTAgency columns to ParnerName,AgencyName by @nthusi-codes in #97
• called columns when inserting data by @cbrianbet in #98
• Updates by @DennisGibz in #101
• Feature/Adapting-DimPatient-to-include-Prep-patients by @nobert-mumo in #100
• Update by @DennisGibz in #104
• Feature update by @DennisGibz in #105
• Feature/Adding-an-intermediate-table-for-latest-Prep-visit-per-patient by @nobert-mumo in #102
• Updates to OptimizeStartRegimens, vlOutcomeAndUptake, AggregateTimeToARTGrp and DimPatient by @cbrianbet in #99
• Loading fact HtsPartnerNotification table by @nobert-mumo in #95
• Updated columns in Linelist art by @cbrianbet in #108
• Explicit date keys on HTS table by @nobert-mumo in #106
• Feat/reportingrates by @cbrianbet in #111
• Create load_aggregate_treatmentoutcomes.sql by @cbrianbet in #107
• Feat/viralloads by @cbrianbet in #110
• Load fact tables by @Marymary-dev in #89
• Added ReportedCCCnumber to hts tests fact by @cbrianbet in #114
• Feauture changes to intermediate tables by @Marymary-dev in #117
• Adding PrepRefils intermediate table by @Marymary-dev in #116
• Feature/ modelling a fact prep table by @nobert-mumo in #115
• Fix/some-changes-to-Dim-Patients-to-deal-with-dups by @nobert-mumo in #113
• Feature update by @DennisGibz in #118
• Resolve file location by @DennisGibz in #119
• Create load_fact_manifest.sql by @cbrianbet in #121
• Fixing Dim Patient foreign keys by @nobert-mumo in #120
• updated sps by @cbrianbet in #122
• changes to ARToutcome by @Marymary-dev in #123
• Remove PatientPK hash function in ct fact tables by @nthusi-codes in #124
• Removed Colations and Hashed columns in ODS Scripts for Hts and Mnchs by @meggmumbi in #126
• Removing-Collation-in-FACTAdverseEvents by @Marymary-dev in #125
• correct dhis2 pull by @cbrianbet in #127
• Adding Setting column to HTSTests by @nobert-mumo in #128
• Adpating ARTPatient to use merge by @DennisGibz in #130
• Feature/Adding defaulter tracing fact table by @nobert-mumo in #129
• Updating hts clients tes and historical tx curr by @nobert-mumo in #131
• Updated cleaning scripts to match the ods tables by @DennisGibz in #133
• load aggregate clientselftest by @meggmumbi in #132
• Feat/re-aranging folder structure for cleaning scripts by @nobert-mumo in #135
• Aggregate htspnschildren reformatted by @meggmumbi in #136
• Created aggregates and linelist for HTS by @cbrianbet in #134
• Moved to main folder by @cbrianbet in #137
• Update load_aggregate_hts_tbscreening.sql by @cbrianbet in #138
• minor correction on pns children by @cbrianbet in #140
• Additions for HTS docket cleaning by @Marymary-dev in #139
• Removing patient id from partition by @Marymary-dev in #143
• Updates to look up tables and update statements by @Marymary-dev in #141
• Corrected OTZ tables by @cbrianbet in #145
• Fix/Update-HTS-Tests-data-query by @nobert-mumo in #146
• Adding a folder restructure by @nobert-mumo in #148
• Fix/Updating Prep logic and few other fixes by @nobert-mumo in #144
• Adding new cleaning scripts for HTS by @nobert-mumo in #142
• Removing PatientIDs from all intermediate tables by @Marymary-dev in #149
• re-arranged the remove duplicate folders by @DennisGibz in #147
• Correct classification of Exits by @Marymary-dev in #150
• Excluding the dead from TXCurr by @Marymary-dev in #151
• Fix/Removing-duplicates-from-OVC-fact-table by @nobert-mumo in #153
• Changes to look up art regimen by @Marymary-dev in #152
• Feature/Prep-Cleaning-Scripts by @nobert-mumo in #155
• Fact prep downstream tables by @Marymary-dev in #160
• Refactoring the ARTOutcome Logic by @Marymary-dev in #157
• Historical appointmentstatus model by @Marymary-dev in #158
• Review 1900 dates in ct by @Marymary-dev in #159
• Feature/Breaking-up-Fact-Prep-into-multiple-tables by @nobert-mumo in #163
• Aggregate appointments by @Marymary-dev in #162
• Task/create-folder-for-short-term-RRI-models by @nobert-mumo in #164
• Prep fact new tables by @Marymary-dev in #161
• Prep Aggregates by @meggmumbi in #156
• Adding test type to the partition to include Initial testers by @Marymary-dev in #172
• Feature/Aggregate-models-for-PMTCT-RRI - first part by @nobert-mumo in #168
• Added prepCT by @cbrianbet in #173
• Feature/aggregate-models-for-the-pmtct-rri-part-2 by @nobert-mumo in #170
• Fix/Add-new-indicator-for- missed prophylaxis PMTCT by @nobert-mumo in #174
• Art outcome effectived discontinuationchanges by @Marymary-dev in #175
• Changes to prep factVisits and aggregates fixes by @cbrianbet in #177
• The Lazarus Effect by @Marymary-dev in #178
• Missed Maternal HAART by @Marymary-dev in #165
• Adding USHAURI PMTCT data by @Marymary-dev in #179
• Missed viral load pmtctrri by @Marymary-dev in #169
• Bugfix/Fixing-OTZ-fact-table-dates by @nobert-mumo in #176
• Fact prep downstream tables by @Marymary-dev in #167
• Reviewing Aggregate_HTSClientSelfTested by @Marymary-dev in #180
• Removing partion and using ANC Visit Number instead by @Marymary-dev in #181
• Feature/Update-of-HTS-indicators-on-aggregate-tables by @nobert-mumo in #182
• Bugfix/-Fixing-hts-aggregate-model by @nobert-mumo in #185
• Reviewing Historical Appointments model by lagging visit dates by @Marymary-dev in #183
• Feauture fact and aggregate appointments by @Marymary-dev in #186
• Adding aggregateARTHistory by @Marymary-dev in #187
• Picking tests that are only in initial test type by @Marymary-dev in #188
• Feature/Aggregate-tables-for-Defaulter-tracings by @nobert-mumo in #184
• restoring the Original model by @Marymary-dev in #189
• Bugfix/Fix-unhashed-NUPI-on-DimPatient by @nobert-mumo in #190
• Create load_aggregate_vl_outcome_and_uptake.sql by @cbrianbet in #191
• Update load_aggregate_vl_outcome_and_uptake.sql by @cbrianbet in #192
• Update load_aggregate_vl_outcome_and_uptake.sql by @cbrianbet in #193
• add vl result to linelistart by @cbrianbet in #194
• Bugfix/Reorder-the-columns-in-AggregateAppointments by @nobert-mumo in #195
• Fix/Adjust-logic-for-getting-viral-loads-intervals-from-start-ART-date by @nobert-mumo in #196
• Fix/Update-column-names-that-start-with-numbers by @nobert-mumo in #197
• Feature/Improve-age-group-naming-to-enable-easy-sorting by @nobert-mumo in #200
• Feauture Adding Linelist_Appointments by @Marymary-dev in #201
• Feature/Viral-Load-guidelines-based-on-new-guidelines by @nobert-mumo in #199
• Renaming mfl codes by @Marymary-dev in #203
• Feauture adding identifiers by @Marymary-dev in #204
• Add-ARTOutcome-Description-column-on-all-datasets-with-ARTOutcome by @nobert-mumo in #205
• Feauture last patient encounter by @Marymary-dev in #208
• Adding load dates in all the models by @Marymary-dev in #209
• Feature/Clients-with-the-ARTOutcome-FV-who-do-not-have-a-Last-Encounter-Date by @nobert-mumo in #210
• Feature/On-Covid-aggregate-adding-a-'Not-accessed'-value-on-Vaccination-status-where-the-values-are-null/blanks & Renaming adult_count column by @nobert-mumo in #207
• Self-Service Partners Training Issues fix by @meggmumbi in #198
• Feauture adding load dates to all tables by @Marymary-dev in #211
• Correct VL Column naming in load_aggregate_OptimizeStartRegimens.sql by @cbrianbet in #212
• Removing gender by @Marymary-dev in #213
• Correct script for HTS_DHIS2 by @cbrianbet in #214
• Bugfix/Fixing-NextAppointment-date-logic by @nobert-mumo in #215
• Added ValidVLResultCategory2 for otz charts by @cbrianbet in #219
• Fix on current Regimen Column by @cbrianbet in #218
• Changes to compute MMD Uptake from 84 days to 90 days by @Marymary-dev in #220
• Organising the code and removing comments by @Marymary-dev in #216
• Set To Nulls where the dates are less than the year 2004 by @Marymary-dev in #221
• Feature/Aggregate-tables-able-to-plot-time-series-data- Part 1 by @nobert-mumo in #222
• Nullify Non plausible Blood pressure values by @Marymary-dev in #223
• Correct column for agencyName by @cbrianbet in #225
• Adding intermediate_ Latest observations by @Marymary-dev in #229
• Feature/Aggregate-tables-able-to-plot-time-series-data-Part-2 by @nobert-mumo in #224
• Pregnant & breastfeeding mothers addition by @Marymary-dev in #226
• Feature/Add-aggregate-data-on-HTSRisk-Scores-&-Categories by @nobert-mumo in #228
• Feature/Add no of screened on aggregate covid by @nobert-mumo in #230
• Feature/Change-the-age-brackets-cut-off-of-OTZ-from-10-24-to-10-19-in-the-DWH-logic by @nobert-mumo in #232
• load_linelist_ovc_enrollment_fix by @cbrianbet in #233
• Adding Differentiated care column by @Marymary-dev in #235
• Adding asof Date to use in BI tools by @Marymary-dev in #236
• Feature/Improve-TXCurr-Historical-query by @nobert-mumo in #234
• Changing from AggregateCohortRetention to AggregateTxNew by @Marymary-dev in #240
• Changes to Elicitation and testing logic by @Marymary-dev in #238
• filtering out the data by @Marymary-dev in #239
• Feature/Move-logic-of-flagging-Not-Assessed-clients-on-Covid-from-the-the-Fact-table-to-the-reporting-tables by @nobert-mumo in #241
• Feauture otz changes by @Marymary-dev in #242
• Adding isHTS_ML by @Marymary-dev in #237
• Included voided check when populating dim dimension by @DennisGibz in #246
• Delete Scripts/REPORTING/Load_LineListHTSRiskCategorizationAndTestRes… by @cbrianbet in #249
• Feature/Creating-Fact-HEIs by @nobert-mumo in #243
• Feature/Creating-Aggregate-HEIs-and-Linelist-HEIs by @nobert-mumo in #244
• Delete Scripts/REPORTING/Load_LinelistPrep.sql by @cbrianbet in #247
• Delete Scripts/REPORTING/Load_all_EMRSites.sql by @cbrianbet in #248
• Added voided cleanup on ct_patient by @cbrianbet in #250
• Adding numer of patients booked in appointment status by @Marymary-dev in #231
• Delete-Scripts/ODS/load_intermediate_tables/CT_intermediate_tables/10_load_intermediate_LatestObs.sql by @nobert-mumo in #252
• Feature/Include-DSD-Models-in-the-missed-appointment-linelist by @nobert-mumo in #253
• Bugfix/StabilityAssement-has-multiple-values-for-Stable-in-Superset by @nobert-mumo in #251
• Feature/Improvements-to-OVC-linelist-by-introducing-new-columns- by @nobert-mumo in #255
• Feature/Include-the-outcome-variables-in-the-OVC-aggregate-and-line-list-datasets by @nobert-mumo in #256
• Adding FACT PBFW by @Marymary-dev in #254
• Adding AgeGroup Key by @Marymary-dev in #257
• Adding Aggregate PBFW by @Marymary-dev in #259
• Feature/Add-a-column-for-referred-for-HIV-preventive-services-in-the-LineListHTSRiskCategorizationAndTestResults by @nobert-mumo in #262
• Update on voided column where voided is null to 0 by @DennisGibz in #264
• Fix/OTZ-eligibilty-and-enrollment-linelist by @nobert-mumo in #265
• Adding Viral load indicators by @Marymary-dev in #263
• Feature/-Adding-a-linelist-report-for-preganat-and-breast-feeding-moms by @nobert-mumo in #258
• Feauture adding pbfw fact by @Marymary-dev in #268
• Refactor to filter out Repeat VLs by @Marymary-dev in #267
• aligning the dimensions to enhance readability by @DennisGibz in #261
• Feature/Adding-additional-attributes-to-line-list-PBFW by @nobert-mumo in #269
• Adding VL indicators by @Marymary-dev in #270
• Adding WHO Stahe by @Marymary-dev in #271
• Bugfix/Rename-changed-column-on-linelist-viral-load by @nobert-mumo in #273
• Adding the latest CD4 and CD4 percentage from the Labs dataset by @Marymary-dev in #275
• Adding a comma after PatientPkHash column by @Marymary-dev in #276
• Changing the case from TestName to reference TestResult by @Marymary-dev in #279
• Create load_aggregate_ct_dhis2.sql by @cbrianbet in #274
• Remove SSIS directory by @nthusi-codes in #272
• Modified DimPatient to include voided column. Included where voided= … by @DennisGibz in #277
• Adding TB Screening to Latest Obs by @Marymary-dev in #280
• Feature/modelling-NCD-indicators by @nobert-mumo in #278
• Feature/Adding-additional-indicators-on-NCDs-on-the-reporting-layer by @nobert-mumo in #283

New Contributors

• @nobert-mumo made their first contribution in #2
• @Marymary-dev made their first contribution in #8
• @nthusi-codes made their first contribution in #28
• @DennisGibz made their first contribution in #101
• @meggmumbi made their first contribution in #126

Full Changelog: https://github.com/palladiumkenya/dwh-etl/commits/v1.0.0