Full Level 2 and 3 Data Definition 2009/2010

L_FULLLEVEL2, L_FULLLEVEL3

DATA DEFINITION

1. Full NVQ Level 2 and Full NVQ Level 3 Learner Flags.

PURPOSE

2. It is designed to identify the volume of full National Vocational Qualification (NVQ) level 2 and 3 provision being delivered.

RELEVANT COLLECTIONS

ILR (LR)
ILR (ER)
ILR (UFI)
ILR (ASL)
ILR (ESF SR)

SOURCE DATA

3. The following variables are used as source data for the calculation of Full NVQ Level 2 and Full NVQ Level 3 Learner Flags.

Field Name	Label	Dataset
A15	A15 Programme type	Aims Standard File
A_ACTIVE	A_ACTIVE Aim Active for the Current Academic Year	Aims Standard File
A_L2CAT	A_L2CAT Full Level 2 Category	Analytical LAD
A_L2PCT	A_L2PCT Full Level 2 Pct	Analytical LAD
A_L3CAT	A_L3CAT Full Level 3 Category	Analytical LAD
A_L3PCT	A_L3PCT Full Level 3 Pct	Analytical LAD
A_L2WID	A_L2WID Level 2 Width for Full Level 2 Aims	Aims Standard File
A_L3WID	A_L3WID Level 3 Width for Full Level 3 Aims	Aims Standard File

DERIVED VARIABLES AND DATASETS

4. The definition produces the following derived variable(s)

Field Name	Label	Dataset
L_FULLLEVEL2	L_FULLLEVEL2 Full Level 2 Learner	Learner, Aims, ESF and HE Standard Files
L_FULLLEVEL3	L_FULLLEVEL3 Full Level 3 Learner	Learner, Aims, ESF and HE Standard Files

VALUES

5. The table below outlines the categories for L_FULLLEVEL2, L_FULLLEVEL3

Value	Label
-1	Not Applicable/Not Known*
0	No
1	Yes

*Applies to system missing values and where there are non-active aims.

DETAILED DEFINITION

6. Full NVQ Level 2 Learner Flag (L_ FULLLEVEL2) is calculated for learners that are in the Full Level 2 Entitlement Category (A_L2CAT). If the Full Level 2 % (L_L2PCT) is greater than 100, then they have a value of 1 for Full NVQ Level 2 Learner Flag (L_ FULLLEVEL2).

7. Full NVQ Level 3 Learner Flag (L_ FULLLEVEL3) is calculated in the same way but for learners that are in the Full Level 3 Entitlement Category (A_L3CAT). If the Full Level 3 % (L_L3PCT) is greater than 100, then they have a value of 1 for Full NVQ Level 3 Learner Flag (L_FULLLEVEL3). 8 When calculating the learner level flags (L_FULLLEVEL2, L_FULLLEVEL3), aims that are not active (A_ACTIVE=0) are excluded.

FULL LEVEL SAMPLE CODE

1. The following SPSS code is provided to illustrate the Full Level 2 and 3 data definition.

L_FULLLEVEL2

2. The following table shows the steps required to derive L_FULLLEVEL2

Step	Condition	Action/Action if true	Action if false
1	Does A_L2CAT = 1 or 4	Set A_L2WID = A_L2PCT	Set A_L2WID to 0
2	Does A15 = 3	Set A_L2WID = 100	Set A_L2WID to 0
3	Does A15 = 10	Set A_L2WID = 0
4	(Aggregation) For all aims where the Learner (L03) and provider (L01) are the same and A_ACTIVE=1.	Set L_L2PCT= sum of all the A_L2WID for the learner. Go to 5.	-1
5	Is L_L2PCT>=100	Set L_FULLLEVEL2 to 1	Set L_FULLLEVEL2 to 0

Step	Condition	Action/Action if true	Action if false
1	Does L_FULLLEVEL2 contain a value	None	Set L_FULLLEVEL2 = -1

3. The following SPSS code illustrates how to derive L_FULLLEVEL2.

GET FILE =‘………..AIMS data set’
SORT CASES BY A09.
MATCH FILES
 /FILE = *
 /TABLE = …..Analytical LAD data set
 /BY A09.
/* Aims where A_L2CAT=1 or 4 are aims that count towards a full level 2.
RECODE A_L2CAT ('1', '4' = 1) (ELSE = 0) INTO A_LEVEL2.
/* Create a variable A_L2WID which will contain the percentage contribution towards a full level 2 for each aim.
COMPUTE A_L2WID = 0.
/* Where the aim counts towards a full level 2 assign the percentage towards a full level 2 to the variable A_L2WID.
IF A_LEVEL2 = 1 A_L2WID = A_L2PCT.
/* An Advanced Apprenticeship counts as a full level 2, A_L2WID is set to 100%. 
/* A Higher Level Apprenticeship does not count as a full level 2 as it is level 4, A_L3WID is 0%.
IF A15=3 A_L2WID=100.
IF A15=10 A_L2WID=0.
SELECT IF A_ACTIVE=1.
/* Aggregate to learner level.
AGGREGATE OUTFILE = *
 /BREAK L01 L03
 /L_L2PCT = SUM (A_L2WID).
/* Where the learner level percentage towards a full level 2 is 100 or more, then the learner is counted as a full level 2 learner.
COMPUTE L_FULLLEVEL2 = 0.
IF L_L2PCT >= 100 L_FULLLEVEL2 = 1.
SAVE OUTFILE=……L_L2PCT.SAV’
GET FILE=’…...LEARNER Dataset’.
SORT CASES L01 L03.
MATCH FILE
/FILE= *
/TABLE=’…… L_L2PCT.SAV’.SAV’
/BY L01 L03.
RECODE L_FULLLEVEL2(SYSMIS=-1).
VALUE LABELS L_FULLLEVEL2
 -1 'Not Applicable/Not Known'
 1 'Yes'
 0 'No'.
VARIABLE LABELS L_FULLLEVEL2 "L_FULLLEVEL2 Full level 2 learner".

4. The following SQL code illustrates how to derive L_FULLLEVEL2:

select B.L01, B.L03 , case when L_L2PCT>=100 then 1 when L_L2PCT is NULL then -1 else 0 end as L_FULLLEVEL2 from ( select L01, L03, count(A05) as aims , sum(A_L2WID) as L_L2PCT from ILR0910_L01_AIMS A join LAD_0910 B on A.A09=B.A09 where A_ACTIVE=1 group by L01, L03 )T right outer join ILR0910_L01_LEARNER B on T.L01=B.L01 and T.L03=B.L03 order by B.L01, B.L03

select

      B.L01, B.L03
     , case when L_L2PCT>=100 then 1
     when L_L2PCT is NULL then -1
     else 0 end as L_FULLLEVEL2

from
(
select

L01, L03, count(A05) as aims
, sum(A_L2WID) as L_L2PCT

from

ILR0910_L01_AIMS A

join

LAD_0910 B

on A.A09=B.A09

where A_ACTIVE=1

group by

L01, L03
)T

right outer join ILR0910_L01_LEARNER B

on T.L01=B.L01 and T.L03=B.L03

order by

B.L01, B.L03

L_FULLLEVEL3

5. The following table shows the steps required to derive L_FULLLEVEL3

Step	Condition	Action/Action if true	Action if false
1	Any	Set A_L3WID =
2	Does A_L3CAT = 1, 2 or 3	Set A_L3WID = A_L3PCT	Set A_L3WID to 0
3	Does A15 = 2	Set A_L3WID = 100	Set A_L3WID to 0
4	Does A15 = 10	Set A_L3WID = 0
5	(Aggregation) For all aims where the Learner (L03) and provider (L01) are the same and A_ACTIVE=1.	Set L_L3PCT= sum of all the A_L3WID for the learner. Go to 6.
6	Is L_L3PCT>=100	Set L_FULLLEVEL3 to 1	Set L_FULLLEVEL3 to 0

Step	Condition	Action/Action if true	Action if false
1	Does L_FULLLEVEL3 contain a value	None	Set L_FULLLEVEL3 = -1

6. The following SPSS code illustrates how to derive L_FULLLEVEL3.

GET FILE ‘…………AIMS data set’
SORT CASES BY A09.
MATCH FILES
 /FILE=*
 /TABLE=Analytical LAD data set
 /BY A09.
/* Aims where A_L3CAT=1, 2 or 3 are aims that count towards a full level 3.
RECODE A_L3CAT ('1', '2', '3' = 1) (ELSE = 0) INTO A_LEVEL3.
/* Create a variable A_L3WID which will contain the percentage contribution towards a full level 3 for each aim.
COMPUTE A_L3WID = 0.
/* Where the aim counts towards a full level 3 assign the percentage towards a full level 3 to the variable A_L3WID.
IF A_LEVEL3 = 1 A_L3WID = A_L3PCT.
/* An Advanced Apprenticeship counts as a full level 3, A_L3WID is set to 100%. 
/* A Higher Level Apprenticeship does not count as a full level 3 as it is level 4, A_L3WID is 0%.
IF A15=2 A_L3WID=100.
IF A15=10 A_L3WID=0.
SELECT IF A_ACTIVE=1.
/* Aggregate to learner level.
AGGREGATE OUTFILE = *
 /BREAK L01 L03
 /L_L3PCT = SUM (A_L3WID).
/* Where the learner level percentage towards a full level 3 is 100 or more, then the learner is counted as a full level 3 learner.
COMPUTE L_FULLLEVEL3 = 0.
IF L_L3PCT >= 100 L_FULLLEVEL3 = 1.
SAVE OUTFILE=……L_L3PCT.SAV’
GET FILE=’…...LEARNER Dataset’.
SORT CASES L01 L03.
MATCH FILE
/FILE= *
/TABLE=’…… L_L3PCT.SAV’.SAV’
/BY L01 L03.
RECODE L_FULLLEVEL3 (SYSMIS=-1).
VALUE LABELS L_FULLLEVEL3
 -1 'Not Applicable/Not Known'
  1 'Yes'
  0 'No'.
VARIABLE LABELS “L_FULLLEVEL3 Full level 3 learner".

7. The following SQL code illustrates how to derive L_FULLLEVEL3:

select B.L01, B.L03 , case when L_L3PCT>=100 then 1 when L_L3PCT is NULL then -1 else 0 end as L_FULLLEVEL3 from ( select L01, L03, count(A05) as aims , sum(A_L3WID) as L_L3PCT from ILR0910_L01_AIMS A join LAD_0910 B on A.A09=B.A09 where A_ACTIVE=1 group by L01, L03 )T right outer join ILR0910_L01_LEARNER B on T.L01=B.L01 and T.L03=B.L03 order by B.L01, B.L03

select

     B.L01, B.L03
     , case when L_L3PCT>=100 then 1
     when L_L3PCT is NULL then -1
     else 0 end as L_FULLLEVEL3