Output Table

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

Context

This script creates the list of industry training qualifications that an individual has attained and the date it wasthey were attained in a spell-based format. The business key of the output spells is expected to be one row per individual per reporting year per course code (PMS & ITR) per ITL fund code per ITL provider. However, it might be best to ascertain the business key of the source table with MoE, as it is not clear what combination of columns can give usresults in a unique row in the source table.

Key Business Rules

1. Note that course codes have undergonewent a source-system change in 2011- t. The pre-2011 course codes are represented by the “PMS” course codes and post-2011 codes given by “ITR” course codes. Similarly, the industry classification of the ITO qualification can be obtained from NZSCED (mostly available before 2011, partly available post-2011) and ANZSIC codes (only available post-2011).
2. The Industrialy Training Organisation (ITO) that provides the training is available from the EDUMIS ID (represented in this spell as provider_code).
3. Currently, the qualification attained date for the ITO qualification is defined as the last day of the year in which the training activity is reported to the Tertiary Education Commission. This is a pseudo-date, and merely reflective of the date of reporting. tThe actual date on which the qualification was attained might be different from this. Alternatively, it might be better to use the date of trainee exit from the ITO programme, which is also available in the source data. This possibility needs to be verified further and should be verified with subject matter experts. The proposed alternative method is to use the following logic:
   • Use the date of trainee exit wherever available-, and if it is absent,
   • Use the duration of course added to the course start date, and if either of these are absent,
   • Use the reporting year as the qualification attained date.
4. The ITO 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 attained date and qualification expiry date.
5. The scope of the spell excludes any LCP (Limited Credit Programmes) types as these are non-qualification programmes. SCP (Supplementary Credit Programmes) types are also non-qualifying, but this needs to be verified with MoE and meantime they are currently retained in the spells. (moe_itl_level1_qual_awarded_nbr + moe_itl_level2_qual_awarded_nbr + moe_itl_level3_qual_awarded_nbr + moe_itl_level4_qual_awarded_nbr + moe_itl_level5_qual_awarded_nbr + moe_itl_level6_qual_awarded_nbr + moe_itl_level7_qual_awarded_nbr) > 0
6. Regarding derivation of NZQF: The individual need not necessarily complete a full training programme to have an NZQF level. If they have at least one of the 8 qualification levels identified in the ITO source data, they are awarded a corresponding pseudo-NZQF level and included in the spell output. The business logic for this estimated NZQF level has been derived through consultations with Ministry of Education. The business logic is as follows-
   • If the number of Level 7 qualifications awarded (“moe_itl_level7_qual_awarded_nbr” column) is greater than 0, then NZQF level is 7.
   • Repeat previous step for each of the levels until Level 1.
   • If all of these are less than 1 or NULL, then assume NZQF level to be “0” to represent Unknown values.
7. A “Level-10 ID” column has been created as an output column for this dataset - this is a supposed alternative to the NZ Qualifications Framework levels (NZQF) and areis used internally by the Ministry of Education. The rationale behind this usage is not clear, but the column has been added here nevertheless. This business logic here maps the Qualifications Awarded columns directly to create the Level-10 IDs, except that:
   • NZQF levels 5 and 6 are mapped to “5” on the Level-10 scale, and
   • NZQF level 7 is mapped to “6” on the Level-10 scale

Open Questions

There are still several clarifications required from subject matter experts on this dataset:

• Why is NZQF level in the table not used?
• Deriving the qualification attained using the ITL start and end dates and duration of course
• Filtering out all records where there are no qualifications. While the rationale makes perfect sense, it is not clear if this filter actually does what we believe it does, and should be confirmed with MoE.
• The origin of Level 10 ID mapping logic is unclear - is this obtained from MoE?
• Which version of ANZSIC codes are being used in the data? Can we safely assume ANZSIC06?
• Why are LCP programmes being filtered out? Since we already filter out LCP programmes, should SCP also be filtered out since SCPs are non-qualifying programmes too?

Inputs

1. IDI_UserCode: 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 that havewith the same name.

Outputs

Version Log

List of key contacts from the Community of Interest

Code

/* Establish database for writing views */
/*
:setvar targetdb "IDI_UserCode"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "[IDI_Clean_202310]"
*/

:setvar targetdb "IDI_UserCode"
:setvar targetschema "DL-MAA2020-47"
:setvar projprefix "tmp"
:setvar idicleanversion "IDI_Clean_202306"
GO

/* Delete the database object if it already exists */
USE $(targetdb)
GO

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

CREATE VIEW [$(targetschema)].[$(projprefix)_moe_ito_quals] AS

--<!

select  
    a.snz_uid
    ,cast('ITL' as varchar(50)) as data_source
    ,a.moe_itl_pms_course_nbr as qual_pmscourse_pre2011
    ,a.moe_itl_itr_course_nbr as qual_itrcourse_post2011
    ,a.moe_itl_nzsced_code as nzsced_industry_code
    ,a.moe_itl_anzsic_code as anzsic_industry_code
    ,a.moe_itl_fund_code as itl_fund_code
    ,a.moe_itl_ito_edumis_id_code as provider_code
    ,a.moe_itl_programme_type_code as programme_type_code
    ,datefromparts(a.moe_itl_year_nbr, 12, 31) as qual_attained_date
    ,datefromparts(9999, 12, 31) as qual_expiry_date
    ,a.moe_itl_start_date as trainee_prog_entry_date
    ,a.moe_itl_end_date as trainee_prog_exit_date
    ,case  
        when a.moe_itl_level7_qual_awarded_nbr>=1 then 7
        when a.moe_itl_level6_qual_awarded_nbr>=1 then 6
        when a.moe_itl_level5_qual_awarded_nbr>=1 then 5
        when a.moe_itl_level4_qual_awarded_nbr>=1 then 4
        when a.moe_itl_level3_qual_awarded_nbr>=1 then 3
        when a.moe_itl_level2_qual_awarded_nbr>=1 then 2
        when a.moe_itl_level1_qual_awarded_nbr>=1 then 1
        else 0 
    end as nzqflevel
    /* Counts of number of qualifications awarded, by NZQF level*/
    ,a.moe_itl_level1_qual_awarded_nbr
    ,a.moe_itl_level2_qual_awarded_nbr
    ,a.moe_itl_level3_qual_awarded_nbr  
    ,a.moe_itl_level4_qual_awarded_nbr
    ,a.moe_itl_level5_qual_awarded_nbr
    ,a.moe_itl_level6_qual_awarded_nbr
    ,a.moe_itl_level7_qual_awarded_nbr      
    ,a.moe_itl_programme_completed_ind
from [$(idicleanversion)].[moe_clean].tec_it_learner a
where 
    /* Below filter is based on MoE's advice. LCP is a non-qualification programme. */
    moe_itl_programme_type_code not in ('LCP')
    /* Filter out all records where no qualification has been awarded - these are deemed unnecessary since we 
        want to look at qualifications obtained and derive NZQF levels- <CHECK WITH TECH LEADS>*/
    and (moe_itl_level1_qual_awarded_nbr + moe_itl_level2_qual_awarded_nbr + moe_itl_level3_qual_awarded_nbr
        + moe_itl_level4_qual_awarded_nbr + moe_itl_level5_qual_awarded_nbr + moe_itl_level6_qual_awarded_nbr
        + moe_itl_level7_qual_awarded_nbr) > 0;
GO

--!>