Outputs:

SQL: [IDI_Community].[edu_quals_moe_ttr_prereq].[quals_moe_ttr_prereq_202603]
SAS: libname cm_quals_moe_ttr_prereq dsn=IDI_community_srvprd schema=edu_quals_moe_ttr_prereq; run ;
How to access a code module in the Data Lab: Read here

Inputs:

Output table

SQL: [IDI_Community].edu_quals_moe_ttr_prereq.quals_moe_ttr_prereq_YYYYMM
SAS: libname cm_ttr_pre ODBC dsn=IDI_community_srvprd schema=edu_quals_moe_ttr_prereq;
How to access a code module in the Data Lab : Read here

Context:

Targeted training programmes provide courses for people, particularly school leavers with low or no qualifications, and those who are at risk of long term unemployment. See here.

The Targeted Training (and apprenticeship) fund was introduced in July 2020 to support learners to undertake vocational education and training without fees. It targeted industry skill needs, where demand from employers for these skills was expected to grow during New Zealand’s recovery period from the impacts of COVID-19. See here.

The source dataset contains training activity information for people in targeted training programmes:
Gateway, Skill Enhancement, Training Opportunities, Foundation Focussed Training Opportunities and Youth Training.

This module uses the prerequisites when people apply to enter targeted training programmes as a source of data on qualifications achieved. A known weakness is that this is not data on when they achieved the qualification, instead it is data on when they notified TEC of these qualifications.

Stats NZ’s Administrative Population Census (APC) data uses an experimental approach to derive census information from administrative (admin) data.

Highest qualification is a variable that is available in the AOC data. Prerequisites to enrol in studies including, training programmes and tertiary education programmes, are considered within APC to calculate teh highest qualification. This code module uses the APC prerequisites business rules and
will be another data source used towards the highest qualification code module.

Key Business Rules

From the logic for the APC tables we get the business rules below:

1. If there is (‘UNQ’, ‘C11’, ‘C39’, ‘C40’, ‘C80’, ‘C81’, ‘SCA’,‘L/N’), then we will map it to zero
2. If there is (‘SCB’, ‘NC1’, ‘SFA’), then we will map to just a 1
3. If there is (‘SFB’, ‘NC2’), then we will map to just a 2
4. If there is (‘HSC’, ‘UE’),then we will map to just a 3
5. If there is (‘Deg’), then we will map to just a 7
6. Exclusions: (‘TRC’, ‘N/A’, ‘OTH’, ‘UNKNOWN’, ‘GATEW’). These qualifications don’t specify the level because they are too broad
7. 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.

Inputs

1. {targetdb}: The SQL database on which the spell datasets are to be created (when using the GenData version of the script).
2. {targetschema}: The project schema under the target database into which the spell datasets are to be created (when using the GenData version of the script).
3. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that have the same name (when using the GenData version of the script).
4. {idicleanversion}: The IDI Clean version that the spell datasets need to be based on.
5. (REMOVED) {populationdata}: A population table - the output spell tables/views in the spell dataset will be subset only to include the subjects in this population, further filtered to the dates specified for each subject. the table is expected to contain the ID, start date and end date columns
6. (REMOVED) {idcolumn}: The name of ID column in the population table, that identifies a subject of the population. This column is used to filter the database objects- the output spell tables will only contain these subjects.
7. (REMOVED) {startdatecolumn}: The name of start date column in the population table. The events in the spell tables are subset to those intersecting with this column for that subject. Each subject may have a different start & end date.
8. (REMOVED) {enddatecolumn}:The name of end date column in the population table.

Version Log

Community of Interest

Code

/* Set dynamic variables (these come from the parameter file) */
:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"

/* Establish database for writing views (GenData version only) */
USE $(targetdb);
GO

/* Delete the database object if it already exists (GenData version only) */

DROP VIEW IF EXISTS [$(targetschema)].[$(projprefix)_quals_moe_ttr_prereq];
GO
CREATE VIEW [$(targetschema)].[$(projprefix)_quals_moe_ttr_prereq] AS

SELECT DISTINCT [snz_uid]
    , CAST('TART_IT_HIST' AS VARCHAR(50)) AS data_source
    , DATEFROMPARTS([moe_ttr_year_nbr], 12, 31) AS [qual_recorded_date]
    , DATEFROMPARTS(9999, 12, 31) AS [qual_expiry_date]
    , CASE WHEN [moe_ttr_education_hist_code] IN ('UNQ', 'C11', 'C39', 'C40', 'C80', 'C81', 'SCA','L/N') THEN 0  
		 WHEN [moe_ttr_education_hist_code] IN ('SCB', 'NC1', 'SFA') THEN 1
		 WHEN [moe_ttr_education_hist_code] IN ('SFB', 'NC2') THEN 2 
		 WHEN [moe_ttr_education_hist_code] IN ('HSC', 'UE') THEN 3
		 WHEN [moe_ttr_education_hist_code] IN ('DEG') THEN 7
	END AS nzqflevel
    , moe_ttr_moe_prov_code AS provider_code
FROM [$(idicleanversion)].[moe_clean].[targeted_training]
WHERE [moe_ttr_education_hist_code] NOT IN ('TRC', 'N/A', 'OTH', 'UNKNOWN', 'GATEW')
 ;
GO