Output Table

SQL:[IDI_Community].[edu_quals_moe_secondary].quals_moe_secondary_YYYYMM
SAS:
libname cmedqsq ODBC dsn=idi_community_srvprd schema=edu_quals_moe_secondary;
How to access a code module in the Data Lab: Read here

Description

This module will define school achievement on an individual and qualification level. This means someone who has achieved NCEA Levels 1, 2, and 3 will be represented three times. Achievement will be linked to the year the qualification was gained, enabling point-in-time analyses to be completed. This module does not include education spells by school, nor non-secondary qualifications. These will be captured by other modules. It will also not rationalise overseas qualifications with NCEA achievement. There will also be an overarching module which will capture highest qualification gained. This module will be one input into the highest qualification module.

Context

These scripts create the list of secondary school qualifications that an individual has attained and the date on which they were attained in a spell-based format. The corresponding New Zealand Qualifications Framework (NZQF) level is also provided. NZQF qualification levels are as follows:

• Levels 1-4: Certificate level
• Levels 5-6: Diplomas
• Level 7: Bachelor’s Degree, Graduate Diplomas and Certificates
• Level 8: Postgraduate Diplomas and Certificates, Bachelor Honours Degree
• Level 9: Master’s Degree
• Level 10: Doctoral Degree

A large majority of the records in this table relate to National Certificate of Educational Achievement (NCEA) qualifications, although there are other qualifications that are also available in the output. NCEA is the predominant qualification earned at secondary school. There are three levels of NCEA, corresponding to Levels 1-3 of the NZQF. The levels depend on the difficulty of the standards achieved.

At each level, students must achieve a certain number of credits to gain an NCEA certificate. Credits can be gained over more than one year. Students who want to study at a tertiary level can choose from a number of education options ranging from universities to Te Pūkenga (and its subsidiaries), private training establishments, transitional industry training organisations and more. In most cases (especially for higher qualifications), students complete secondary school before studying at tertiary institutions, but some students enrol in tertiary courses instead of senior secondary courses, and other students might be enrolled simultaneously with schools and tertiary providers.

In the case of university, students tend to first complete University Entrance requirements, however, discretionary entrance may be granted to some students who meet certain requirements and special admission is available for New Zealand or Australian citizens over the age of 20 without the requirement to meet University Entrance qualifications.

The expected business key for this spell dataset is one row per student ID, secondary school qualification, attained date, endorsement, awarding school, elective and optional strands. Note that the attained date is an approximation, and is the last day of the year in which the qualification was attained. The attained date is more important than the endorsed date, since endorsements were only added in 2007.

A few duplicate records have been observed in the source data on these business key columns, but with different New Zealand Qualifications Authority (NZQA) load & completion dates - the actual business significance of ignoring these duplicates is unknown. The business meaning of the snz_unique_nbr in the input dataset is also unknown. The data is supplied with the master (National Student Number) NSN from the (National Student Index) NSI system as at the date it is sent. The IDI considers some learners supplied to be duplicates – i.e. data from outside the education data joins them. It is advised to remove learners with duplicate records rather than taking the row with the highest educational attainment. Some records are bound to be falsely merged in IDI, but it will not make a material difference if you choose to merge them.

There are also a large variety of non-NCEA qualifications in the table which together make up almost half the number of records. These may sometimes include tertiary level qualifications, as presented in the Learner BDS system (Ministry of Education’s internal datamart). Reasons for this include:

• NZQA provides MoE a copy of qualifications they hold records for, these include qualifications awarded by tertiary providers. Hence some of the attainment in these files will also be reported through the tertiary qualification tables.

Additional comments:

• Only the school leaver data contains the results from international assessments (mainly Cambridge International Curriculum and International Baccalaureate), although only a very small proportion of the population attain these. If these are not accounted for, some learners will appear to have no attainment, while in reality these students have high-level attainment. When defining an age-based population, it is important to note that these learners will have left school by age 19.

Key Business Rules

1. The spells only include individuals who attained secondary school-level qualifications in New Zealand from 2003 onwards (i.e., after the introduction of NCEA - the National Certificate of Educational Achievement ). This means that any individuals earning secondary school qualifications outside of New Zealand or before 2003 in New Zealand will be absent in the output. The base tables do not have any data on pre-NCEA secondary qualifications like Sixth Form or School Certificates. There are a few records with NCEA found before 2003 - it is unclear how these records came to be, since they pre-date the introduction of NCEA. It is unknown if individuals who earned Sixth-Form transitional qualifications after 2003 are included in the data as an equivalent NCEA qualification. Also note that any International Baccalaureate or Cambridge International Curriculum qualifications are absent from the base table - but these can be obtained from the school-leavers dataset (added in the spell dataset IDI_UserCode.{targetschema}.{projprefix}_moe_schoolleaver_quals).

2. If the qualification was loaded more than 2 years after the attained year, they are removed. Finally, any qualifications with a null or zero NZQF level are also ignored. These rules have been applied on the advice of analysts from Ministry of Education - the rationale for this is currently unknown and needs verification from MoE. Around 20% of the records in this table are filtered out as a result - but for NCEA-specific qualifications, less than 4% has this problem (as at the IDI refresh of September 2020). MoE prefers to use populations starting 2008/2009 since the systems currently used to collect administrative data from schools were rolled out over 2006-2007 and appear to be consistent from 2009 onwards.

3. The script provides certain flags for NCEA-specific qualifications in the output. The intent of these flags is to assume that if an individual qualifies for NCEA levelLevel- 3, they automatically qualify for Level 2 and 1 as well (based on certain endorsement levels). The business logic for these flags originated from the Treasury, and was later verified against Ministry of Education’s own business logic. The definition of these NCEA flags are as follows:

   • If the Qualification Code is one of ‘0928’,‘928’,‘0973’,‘973’,‘1039’ (i.e. any of the NCEA qualification codes) and Endorsement is either ‘Merit’ (M), ‘Excellence’ (E) or ‘No Endorsement’ (ZZ), then the NCEA level 1 is “1”. This assumes that if a student got NCEA L3 or L2 with these endorsement codes, they also qualify for the L1 as well. Otherwise, the flags are set to “0”.
   • If the Qualification Code is one of ‘0973’,‘973’,‘1039’ and Endorsement is either ‘Merit’, ‘Excellence’ or ‘No Endorsement’, then the NCEA level 2 is ‘1’. This assumes that if a student got NCEA L3, it is assumed that they also qualify as for L2 as well. Otherwise, the flag is set to ‘0’.
   • If the Qualification Code is ‘1039’ and Endorsement is either ‘Merit’, ‘Excellence’ or ‘No Endorsement’, then the NCEA level 3 is ‘1’. Else Otherwise it is set to ‘0’.
   • Finally, if the NZQF level is less than 1 or NULL, then these rows are removed. This never happens for NCEA related qualifications, as all NCEA quals have an associated NZQF level.

4. The logic explained above is valid from 2014. In the dataset dated before 2014, there are learners who attained NCEA Level 2 or NCEA Level 3 without meeting the NCEA Level 1 literacy and numeracy requirements.

5. The spell dates are approximations. MoE qualifications data only supplies the calendar year in which a qualification was attained. Hence the qualification attained date is hard-coded to be the last day of the year in which the qualification was attained. The school qualifications are assumed to be valid for a lifetime, and hence the qualification expiry date is conceptually “infinite” and represented as 31-Dec-9999. The spell is effectively the time period between the qualification attainment and qualification expiry.

6. Qualifications not recognised by NZQF, including overseas qualifications, are excluded from the outputs.

7. We have confirmed with MoE and NZQF (Marian Loader and Catherine Edser) that there is no reason for the removal of records that were loaded on the NZQA more than 2 years after the attained year, and the removal orf NULL and “0” NZQF records. NZQF confirmed that attainment of those qualifications is valid regardless of when it’s loaded. Qualifications with a Level of 0 or null may be historical (pre levels) or not technically qualifications, but still valid. The qualifications with NZQF level 0 or missing will stillhowever not be incllcuded in the output table, since the NZQF level, cannot be determined, which is an important variable in these codes, cannot be determined.

References

List of the most important reference documents readers might need to consult with:

• More information about NZQF can be found here: Background to the New Zealand Qualifications Framework » NZQA
• More information about discretionary entrance is provided here: Other school-leaver qualifications

Open Questions

1. There are a few records with NCEA found before 2003 - it is unclear how these records came to be, since NCEA was introduced from 2003 onwards. It was however suggested by MoE that as NCEA was introduced progressively one level per year starting in 2002, whichthis could account for some of those records.

2. Non-NCEA qualifications and tertiary qualifications can also be found in student_qualification table. While this is expected, we note that almost half the table comprises of such records - it is unknown if this is expected behaviour in the source table. It might be worthwhile confirming that this is expected in the source data. The rationale behind removal of records that were loaded on the NZQA more than 2 years after the attained year, and the removal or NULL and “0” NZQF records should also be confirmed with MoE to ensure that these are reasonable to use in this business context.

Inputs

1. {targetdb}: The SQL database on which the spell datasets are to be created.
2. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
3. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets with the same name.
4. {idicleanversion}: The IDI Clean version that the spell datasets need to be based on.
5. {metaversion}: The metadata version that the concordance dataset needs to be based on.

Outputs

Dependencies

{idicleanversion}.[moe_clean].[student_qualification]
{metaversion}.[moe_school].[qualification_concord]

Version Log

List of key contacts from the Community of Interest

Code

/* Establish database for writing views */
/*
:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"
:setvar metaversion "{metaversion}"
*/

-- Example
-- SQLCMD mode

:setvar targetdb "IDI_UserCode"
:setvar targetschema "DL-MAA2020-47"
:setvar projprefix "tmp"
:setvar idicleanversion "IDI_Clean_202410"
:setvar metaversion "IDI_Metadata_202410"
GO


/* Assign the target database to which all the components need to be created in. */
USE $(targetdb)
GO

/* Delete the database object if it already exists */
IF OBJECT_ID('[$(targetschema)].[$(projprefix)_moe_secondary_quals]','V') IS NOT NULL
DROP VIEW [$(targetschema)].[$(projprefix)_moe_secondary_quals];
GO

/* Create the database object */
CREATE VIEW [$(targetschema)].[$(projprefix)_moe_secondary_quals] AS

/* <! */

	select 
		a.snz_uid
		,cast('SCH_QUAL' as varchar(50)) as data_source
		,a.moe_sql_qual_code as qualification_id
		,b.QualificationCode as qualification_code
		,b.QualificationName as qualification_name
		,a.moe_sql_exam_result_code as endorsement_code
		,a.moe_sql_award_provider_code as awarding_school
		,a.moe_sql_electivt_strand_nbr as elective_strand
		,a.moe_sql_optional_strand_nbr as optional_strand
		,datefromparts(a.moe_sql_attained_year_nbr, 12, 31) as qual_attained_date
		,datefromparts(9999, 12, 31) as qual_expiry_date
		/* MoE advises that the quality of "NZQFlevel" variable is not great for early years */
		,b.nqflevel as nzqflevel
		/* These case statements assume that if a student got  NCEA Level 3, and there is no record for Level 2 or Level 1 then
				we count them having Level 2 and Level 1 as well, this logic is valid from 2014 only.*/
		,case	when (b.QualificationCode in ('0928','928','0973','973','1039')) and a.moe_sql_attained_year_nbr >=2014 and a.moe_sql_exam_result_code in ('E','M' ,'ZZ') then 1
				when (b.QualificationCode in ('0928','928')) and a.moe_sql_attained_year_nbr <2014 and a.moe_sql_exam_result_code in ('E','M' ,'ZZ') then 1
				else 0 end as ncea_l1 
		,case	when (b.QualificationCode in('0973','973','1039')) and a.moe_sql_attained_year_nbr >=2014 and a.moe_sql_exam_result_code in ('E','M' ,'ZZ') then 1
				when (b.QualificationCode in('0973','973')) and a.moe_sql_attained_year_nbr <2014 and a.moe_sql_exam_result_code in ('E','M' ,'ZZ') then 1
				else 0 end as ncea_l2  
		,case	when (b.QualificationCode='1039' ) and a.moe_sql_exam_result_code in ('E','M' ,'ZZ') then 1 else 0 end as ncea_l3 
	from [$(idicleanversion)].[moe_clean].student_qualification a
	left join [$(metaversion)].[moe_school].[qualification_concord] b 
		on (a.moe_sql_qual_code=b.qualificationTableId)
	where 
		b.NQFlevel is not null 
		and b.NQFlevel > 0
		and a.moe_sql_attained_year_nbr >= 2003
		and b.NQFlevel <= 11;
GO

/* !> */