Dropped out reasons summary not matching workbook

[Ghislain Hachey]

What is in the workbook pivot as shown below.

Does not match the summary when loading the workbook (broken down in two following figures).

‌

[Ghislain Hachey]

The most obvious immediately apparent is the self-dropped numbers are missing from the summary when loading workbook. Adding those would then seem to match.

[Ghislain Hachey]

That’s a tricky one :) The reason for this is Chuuk loaded bad data which slipped through validations and polluted the dropout list for all years after it. This is an edge case with one of the only remaining drop-down lists (i.e. Dropout Reasons) that has no validations and is not yet pre-populated from a EMIS lookups. The list of Dropout Reasons that appear on the summary when loading a new workbook actually comes from a distinct list of previous years as shown in the snippet of SQL below from the stored procedure [pSurveyOps].[censusStudentOutput].

‌

The cleanest way to address this issue is cleanup the data. Just go to any Chuuk school, download the survey for year 2023. Examine the Dropout Reasons data, you’ll notice not to clean as shown below.

Filter to only show the students that match all the Self Dropped and Self-Dropped values from the Dropout Reasons. Then replace all the dirty values with the correct “Self Dropped” (i.e. no trailing spaces or “-” in the middle). Reload this cleaned up Chuuk workbook from 2023. Then reloading of the Yap workbook should correctly summarize all the dropouts matching the workbook pivot table.

Note that this would not have affected the data in the database but just the summarized on upload was missing a piece.

[Ghislain Hachey]

No need for any change at the moment. More validations to avoid this situation will be done when we finalize all lookups in the workbook. Solution to this particular issue is reload cleaned up data as detailed.

[Ghislain Hachey]

My proposed solution works on my test machine but not in production. Re-opening to investigate further.