Full Level 2 and 3 Achievement Data Definition 2009/2010
L_FULLLEVEL2_ACHIEVE, L_FULLLEVEL3_ACHIEVE
DATA DEFINITION
1. Full NVQ Level 2 and Full NVQ Level 3 Achievement Learner Flags.
PURPOSE
2. It is designed to identify the volume of Learners achieving a full National Vocational Qualification (NVQ) of level 2 and 3.
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 the Full Level 2 and 3 Achievement data definition.
| Field Name |
Label |
Dataset |
| A_ACTIVE |
A_ACTIVE Aim Active for the Current Academic Year |
Aims Standard File |
| 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 |
| A04 |
A404 Data Set Identifier Code |
Aims Standard File |
| A15 |
A15 Programme type |
Aims Standard File |
| A35 |
A35 Learning outcome |
Aims Standard File |
| A36 |
A36 Learning outcome grade |
Aims Standard File |
| A40 |
A40 Achievement date |
Aims Standard File |
| A_ATYPE |
A_ATYPE Type of Aim |
Aims Standard File |
DERIVED VARIABLES AND DATASETS
4. The definition produces the following derived variable(s)
| Field Name |
Label |
Dataset |
| L_FULLLEVEL2_ACHIEVE |
L_FULLLEVEL2_ACHIEVE Full Level 2 Achievement Learner |
Learner, Aims, ESF and HE Standard Files |
| L_FULLLEVEL3_ACHIEVE |
L_FULLLEVEL3_ACHIEVE Full Level 3 Achievement Learner |
Learner, Aims, ESF and HE Standard Files |
VALUES
5. The table below outlines the categories for L_FULLLEVEL2_ACHIEVE, L_FULLLEVEL3_ACHIEVE
| 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 Achievement Learner Flag (L_FULLLEVEL2_ACHIEVE) is calculated for learners whose aims are in the Full Level 2 Entitlement Category (A_L2CAT) and have achieved that aim. If the Full Level 2 % (L_L2PCT) is greater than 100, then they have a value of 1 for Full NVQ Level 2 Achievement Learner Flag (L_ FULLLEVEL2_ACHIEVE).
7. When calculating the learner level flag, aims that are not active are excluded (using A_ACTIVE).
8. Full NVQ Level 3 Learner Achievement Flag (L_FULLLEVEL3_ACHIEVE) is calculated in the same way but for learners whose aims 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 Achievement Learner Flag (L_FULLLEVEL3_ACHIEVE).
Full Level 2 and 3 Sample Code
1. The following SPSS code is provided to illustrate the Full Level 2 and 3 Achievement data definition.
L_FULLLEVEL2_ACHIEVE
2. The following table shows the steps required to derive L_FULLLEVEL2_ACHIEVE
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does A15 = 3 and A04=35 and there is a value in A40 |
Set A_L2WID_ACH = 100 |
Go to 2 |
| 2 |
Does A15<>2,3 or 10 |
Got to 3 |
Set A_L2WID_ACH to 0 |
| 3 |
Does A_ATYPE = 2 AND is the first character in A36= to one of *, A, B, C |
Set A_L2WID_ACH = A_L2WID.
Go to 5 |
Go to 4 |
| 4 |
Does A35 = 1 AND A_ATYPE <> 2. |
Set A_L2WID_ACH = A_L2WID.
Go to 5 |
Set A_L2WID_ACH to 0.Go to 5 |
| 5 |
(Aggregation)
For all aims where the learner (L03) and provider (L01) are the same and A_ACTIVE=1 |
Set L_L2PCT_ACH to the sum of A_L2WID_ACH |
|
| 6 |
Is L_L2PCT_ACH >=100 |
Set L_FULLLEVEL2_ACHIEVE to 1 |
Set L_FULLLEVEL2_ACHIEVE to 0 |
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does L_FULLLEVEL2_ACHIEVE contain a value |
None |
Set L_FULLLEVEL2_ACHIEVE = -1 |
3. The following SPSS code illustrates how to derive L_FULLLEVEL2_ACHIEVE.
|
GET FILE ‘………..Aims data set’.
COMPUTE A_L2WID_ACH=0.
DO IF NOT ANY(A15,2,3,10).
DO IF (A_ATYPE = 2 AND ANY(SUBSTR(LTRIM (A36," "),1,1),"*","A","B","C")).
COMPUTE A_L2WID_ACH = A_L2WID.
ELSE IF (A35 = 1 AND A_ATYPE <> 2).
COMPUTE A_L2WID_ACH = A_L2WID.
END IF.
END IF.
IF A15 = 3 and A04=35 AND NOT SYSMSIS(A40) A_L2WID_ACH=100.
SELECT IF A_ACTIVE=1.
AGG OUT *
/PRESORTED
/BREAK = L01 L03 L35
/L_L2PCT_ACH = SUM (A_L2WID_ACH).
COMPUTE L_FULLLEVEL2_ACHIEVE = (L_L2PCT_ACH >= 100).
VARIABLE LABELS L_FULLLEVEL2_ACHIEVE‘L_FULLLEVEL2_ACHIEVE Full Level 2 Achievement Learner’.
SAVE OUTFILE=……L_L2PCT_ACH.SAV’
GET FILE=’…...LEARNER Dataset’.
SORT CASES L01 L03.
MATCH FILE
/FILE= *
/TABLE=’…… L_L2PCT_ACH.SAV’.SAV’
/BY L01 L03.
RECODE L_FULLLEVEL2_ACHIEVE (SYSMIS=-1).
VALUE LABELS L_FULLLEVEL2_ACHIEVE
-1 'Not Applicable/Not Known'
0 ‘No’
1 ‘Yes’. |
4. The following SQL code illustrates how to derive L_FULLLEVEL2_ACHIEVE:
|
select
B.L01, B.L03
, case when L_L2PCT_ACH>=100 then 1
when L_L2PCT_ACH is NULL then -1
else 0 end as L_FULLLEVEL2_ACHIEVE
from
(
select
L01, L03, count(A05) as aims
, sum(A_L2WID_ACH) as L_L2PCT_ACH
from
(
select
L01, L03, A05
, case when A15=3 and A04=35 and A40 is not NULL then 100
when A15 not in (2,3,10) and A_ATYPE=2 and LEFT(A36,1) in ('*','A','B','C') then A_L2WID
when A15 not in (2,3,10) and A_ATYPE<>2 and A35=1 then A_L2WID
else 0 end as A_L2WID_ACH
from
ILR0910_E_AIMS
where A_ACTIVE=1
)T
group by
L01, L03
)T2
right outer join
ILR0910_L01_LEARNER B
on T2.L01=B.L01 and T2.L03=B.L03
order by
B.L01, B.L03 |
L_FULLLEVEL3_ACHIEVE
5. The following table shows the steps required to derive L_FULLLEVEL3_ACHIEVE
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does A15 = 2 and A04=35 and there is a value in A40 |
Set A_L3WID_ACH = 100 |
Go to 2 |
| 2 |
Does A15<>2,3 or 10 |
Got to 3 |
Set A_L3WID_ACH to 0 |
| 3 |
Does A_ATYPE = 2 AND is the first character in A36 one of *, A, B, C |
Set A_L3WID_ACH = A_L3WID
Go to 4 |
Go to 5 |
| 4 |
Does A35 = 1 AND A_ATYPE <> 2. |
Set A_L3WID_ACH = A_L3WID.
Go to 5 |
Go to 5 |
| 5 |
(Aggregation)
For all aims where the learner (L03) and provider (L01) are the same and A_ACTIVE =1 |
Set L_L3PCT_ACH to the sum of A_L3WID_ACH.
Go to 6 |
Set L_FULLLEVEL3_ACHIEVE to -1 |
| 6 |
L_L3PCT_ACH >=100 |
Set L_FULLLEVEL3_ACHIEVE to 1 |
Set L_FULLLEVEL3_ACHIEVE to 0 |
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does L_FULLLEVEL3_ACHIEVE contain a value |
None |
Set L_FULLLEVEL3_ACHIEVE = -1 |
6. The following SPSS code illustrates how to derive L_FULLLEVEL3_ACHIEVE.
|
GET FILE ‘………..Aims data set’.
COMPUTE A_L3WID_ACH=0.
DO IF NOT ANY(A15,2,3,10).
COMPUTE A_L3WID_ACH = A_L3WID.
ELSE IF (A35 = 1 AND A_ATYPE <> 2).
COMPUTE A_L3WID_ACH = A_L3WID.
END IF.
END IF.
IF A15 = 2 and A04=35 AND NOT SYSMSIS(A40) A_L3WID_ACH=100.
SELECT IF A_ACTIVE=1.
AGG OUT *
/PRESORTED
/BREAK = L01 L03 L35
/L_L3PCT_ACH = SUM (A_L3WID_ACH).
COMPUTE L_FULLLEVEL3_ACHIEVE = (L_L3PCT_ACH >= 100).VARIABLE LABELS L_FULLLEVEL3_ACHIEVE‘L_FULLLEVEL3_ACHIEVE Full Level 3 Achievement Learner’.
SAVE OUTFILE=……L_L3PCT_ACH.SAV’
GET FILE=’…...LEARNER Dataset’.
SORT CASES L01 L03.
MATCH FILE
/FILE= *
/TABLE=’…… L_L3PCT_ACH.SAV’.SAV’
/BY L01 L03.
RECODE L_FULLLEVEL3_ACHIEVE(SYSMIS=-1).
VALUE LABELS L_FULLLEVEL3_ACHIEVE
-1 'Not Applicable/Not Known'
0 ‘No’
1 ‘Yes’. |
7. The following SQL code illustrates how to derive L_FULLLEVEL3_ACHIEVE:
|
select
B.L01, B.L03
, case when L_L3PCT_ACH>=100 then 1
when L_L3PCT_ACH is NULL then -1
else 0 end as L_FULLLEVEL3_ACHIEVE
from
(
select
L01, L03, count(A05) as aims
, sum(A_L3WID_ACH) as L_L3PCT_ACH
from
(
select
L01, L03, A05
, case when A15=3 and A04=35 and A40 is not NULL then 100
when A15 not in (2,3,10) and A_ATYPE=2 and LEFT(A36,1) in ('*','A','B','C') then A_L3WID
when A15 not in (2,3,10) and A_ATYPE<>2 and A35=1 then A_L3WID
else 0 end as A_L3WID_ACH
from
ILR0910_L01_AIMS
where A_ACTIVE=1
)T
group by
L01, L03
)T2
right outer join
ILR0910_L01_LEARNER B
on T2.L01=B.L01 and T2.L03=B.L03
order by
B.L01, B.L03 |
Date last modified: 02nd December 2009
