Exams candidate missing gender data

[Ghislain Hachey]

It seems the gender data available in all the exams workbook loaded into the EMIS is not recorded and thus not available in the analysis (unless there was a match between the candidate and an existing student in the EMIS. For example, let’s take Math G6 school WTH103 as example, all candidates shown below and all have a gender.

Yet in the warehouse tables we get many “unknown” genders as shown below (filtered for our WTH103 example school)

Looking at the examcandidates table no gender are recorded. Only ones with gender available seems to be the ones with a match with an existing student.

‌

[Brian Lewis]

fix(exams): Exam candidate records gender

Resolves #1196

→ <<cset d8e80f035c32>>

[Brian Lewis]

MIEMIS data patched as follows:

UPDATE ExamCandidates
SET excGender = excData.value('(//row/@Gender)[1]', 'nvarchar(4000)') 
WHERE excData.value('(//row/@Gender)[1]', 'nvarchar(4000)') is not null
AND excGender is null

‌

[Ghislain Hachey]

In case the path above comes across some unexpected gender values violating the constraint with lkpGender.

‌

BEGIN TRANSACTION

-- From row data
SELECT COUNT(*)
    , T.Gender
FROM (
    SELECT excID, excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') Gender FROM ExamCandidates
    ) T
GROUP BY Gender


SELECT COUNT(*), excGender FROM ExamCandidates GROUP BY excGender;

UPDATE ExamCandidates
SET excGender = excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') 
WHERE (excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') is not null AND 
    (excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'F' OR excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'M'))
AND excGender is null

SELECT COUNT(*), excGender FROM ExamCandidates GROUP BY excGender;

ROLLBACK

‌

[Ghislain Hachey]

And if like the case in FSM there is already some pesky bad data that crept in then a cleanup like this.

‌

BEGIN TRANSACTION

UPDATE ExamCandidates
SET excData.modify('delete (//row/@GENDER)[1]')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'N/A'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "F"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'FFEMALE'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "M"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Mlale'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "F"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Feamle'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "F"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Fema!e'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "F"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Femaie'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "M"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Male'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "M"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Male'''

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "F"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Female'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "F"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Female'''

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "F"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Feamale'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "F"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Femlae'

UPDATE ExamCandidates
SET excData.modify('replace value of (//row/@GENDER)[1] with "F"')
WHERE excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'Fmale'

-- From row data
SELECT COUNT(*)
    , T.Gender
FROM (
    SELECT excID, excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') Gender FROM ExamCandidates
    ) T
GROUP BY Gender


SELECT COUNT(*), excGender FROM ExamCandidates GROUP BY excGender;

UPDATE ExamCandidates
SET excGender = excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') 
WHERE (excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') is not null AND 
    (excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'F' OR excData.value('(//row/@GENDER)[1]', 'nvarchar(4000)') = 'M'))
AND excGender is null

SELECT COUNT(*), excGender FROM ExamCandidates GROUP BY excGender;

ROLLBACK

‌

[Ghislain Hachey]

A new issue was created to look into and stop the bad gender data #1231