Aim Type Data Definition 2009/2010
A_ATYPE, L_ATYPE
Note: The variable A_NVQLEV has been deleted and replaced with A_NOTIONLEV for 2009/2010, hence where A_NVQLEV was used as source data or as part of the definition last year it has now been replaced by A_NOTIONLEV.
DATA DEFINITION
1. Type of Aim and In Year Aim Type for a Learner.
PURPOSE
2. To identify the type of each aim undertaken by the learner for each academic year, shown at aim and learner level. This is used to categorise the learner/aims and to assess the range and type of provision in the sector to help monitor progress towards national and local targets. These are key fields for analysis.
RELEVANT COLLECTIONS
- ILR (LR)
- ILR (ER)
- ILR (ASL)
- ILR (UFI)
SOURCE DATA
3. The following variables are used as source data for the calculation of Type of Aim and In Year Aim Type for a Learner.
| Field Name |
Label |
Dataset |
| A09 |
A09 Aim Reference |
Aims Standard File |
| A31 |
A31 Learning Actual End Date |
Aims Standard File |
| A_NOTION |
A_NOTION Notional NVQ Level |
Analytical LAD* |
| A_LATYPE |
A_LATYPE Aim Type |
Analytical LAD* |
| A_NOTIONLEV |
A_NOTIONLEV Notional Level of The Aim |
Aims Standard File |
| A_ACTIVE |
A_ACTIVE Aim Active for the current academic year |
Aims Standard File |
| A_INYR_ACTUAL_GLH |
A_INYR_ACTUAL_GLH Aim Actual Current Year GLH |
Aims Standard File |
* The oracle LAD database refers to these fields as notional_nvq_level_id and learning_aim_type_id.
DERIVED VARIABLES AND DATASETS
4. The definition produces the following derived variable(s)
| Field Name |
Label |
Dataset |
| A_ATYPE |
A_ATYPE Type of Aim |
Aims Standard File |
| L_ATYPE |
L_ATYPE In Year Aim Type for the A Learner |
Learner Standard File, Aims Standard File |
VALUES
5. The table below outlines the categories for A_ATYPE, L_ATYPE
| Value |
Label |
| -1 |
Not Applicable/Not Known* |
| 1 |
GCE A/AS/A2 level |
| 2 |
GCSE |
| 3 |
GNVQ precursor |
| 4 |
GNVQ/AVCE |
| 5 |
NVQ |
| 6 |
Access to HE |
| 7 |
HNC/HND |
| 8 |
OCN |
| 9 |
Additional NVQ/GNVQ |
| 12 |
Diploma |
| 10 |
Other |
*Applies to system missing values and where there are non-active aims. Only applicable at learner level.
DETAILED DEFINITION
6. Currently there are over 1,300 aim types in the LAD, which are re-categorised into the 11 broader classifications, in order to provide more meaningful analysis. Type of Aim (A_ATYPE) is calculated using both Learning Aim Type (A_LATYPE) and Learning Aim Reference (A09).
7. Aim type for a learner includes those aims that are active in the current academic year (A_ACTIVE=1).Then the aim with the highest Notional Level of Aim (A_NOTIONLEV) for the learner is used to provide the Aim Type for a Learner (L_ATYPE).
8. Where a learner has more than one aim with the same highest Notional NVQ Level of Aim (A_NVQLEV), and the aim types are the same, then the aim with the highest Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) is used and the corresponding Type of Aim (A_ATYPE) is assigned to the learner to give the Aim Type for a Learner (L_ATYPE). Otherwise, if the aim types are different then the Learner Aim type (L_ATYPE) is set to 10 – other.
Aim Type Sample Code
1. The following SPSS code is provided to illustrate the Aim Type data definition.
A_ATYPE
2. The following table shows the steps required to derive A_ATYPE
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Is A_LATYPE=0001, or 0002, 1413, 1430, 1431, 1432, 1433, 1434 or 1435 |
Set A_ATYPE to 1 |
Go to 2 |
| 2 |
Is A_LATYPE=0003, 1400 or 1422 |
Set A_ATYPE to 2 |
Go to 3 |
| 3 |
Is A_LATYPE=0017, 0028, 0029, 0030, 0886, or 1423 |
Set A_ATYPE to 3 |
Go to 4 |
| 4 |
Is A_LATYPE=0035 or 1414 or 1415 or 1416 or 1417 or 1418 |
Set A_ATYPE to 4 |
Go to 5 |
| 5 |
Is A_LATYPE=0036 or 1326 |
Set A_ATYPE to 5 |
Go to 6 |
| 6 |
Is A_LATYPE=0004 or 0005, 0007 or 1440 |
Set A_ATYPE to 6 |
Go to 7 |
| 7 |
Is A_LATYPE=0031 or 0032 |
Set A_ATYPE to 7 |
Go to8 |
| 8 |
Is A_LATYPE=1442 or 1443 |
Set A_ATYPE to 12 |
Go to 9 |
| 9 |
Where A_LATYPE does not equal any of the above categories |
Set A_ATYPE to 10.Go to 10 |
Go to 10 |
| 10 |
Is A09 of the form ??CN???? |
Set A_ATYPE to 8 |
Go to 11 |
| 11 |
Is A09 of the form ???CN??? |
Set A_ATYPE to 8 |
Go to 12 |
| 12 |
Is A09 of the form CN?????? |
Set A_ATYPE to 8 |
Go to 13 |
| 13 |
Is A09 of the form ZCAC???? |
Set A_ATYPE to 8 |
Go to 14 |
| 14 |
Is A09 of the form XC?????? |
Set A_ATYPE to 8 |
Go to 15 |
| 15 |
Is A09 of the form XBS????? |
Set A_ATYPE to 8 |
Go to 16 |
| 16 |
Is A09 of the form X9V????? |
Set A_ATYPE to 9 |
Go to 17 |
| 17 |
Is A09 of the form Z9V????? |
Set A_ATYPE to 9 |
Go to 18 |
| 18 |
Else Where A_LATYPE does not equal any of the above categories |
Set A_ATYPE to 10 |
|
| Step |
Condition |
Action if true |
Action if false |
| 1 |
Does A_LATYPE contain a value |
None |
Set A_LATYPE = -1 |
3. The following SPSS code illustrates how to derive A_ATYPE
|
GET FILE=’……….Aims dataset’.
SORT CASES A09.
MATCH FILES /FILE=*
/TABLE=’……….Analytical LAD dataset’
/BY A09.
/* Conversion of a_latype (alpha) into broader categories in A_ATYPE (numeric).
RECODE a_latype ('0001','0002','1413',‘1430’,’1431’,’1432’,’1433’,’1434’,’1435’=1)('0003',’1400’,’1422’=2) ('0017','0028','0029','0030','0886',’1423’=3) ('0035','1414','1415','1416','1417','1418'=4)('0036',’1326’=5)('0004','0005','0007',’1440’=6)('0031','0032'=7) (‘1442’,‘1443’=12)(ELSE=10) into A_ATYPE.
/* Assigns the value 8 for aims that are OCN aims
IF SUBST(A09,3,2)='CN' OR SUBST(A09,4,2)='CN' A_ATYPE=8.
IF SUBST(A09,1,2)='CN' A_ATYPE=8.
IF SUBSTR(A09,1,4)=’ZCAC’ A_ATYPE=8.
IF SUBST(A09,1,2)='XC' A_ATYPE=8.
IF SUBST(A09,1,3)='XBS' A_ATYPE=8.
/* Assigns a value of 9 for aim that are generic additional GNVQ units.
IF (SUBSTR(A09,1,3)='X9V') A_ATYPE=9.
IF (SUBSTR(A09,1,3)='Z9V') A_ATYPE=9.
RECODE A_ATYPE (SYSMIS=-1)
VARIABLE LABELS A_ATYPE 'A_ATYPE TYPE OF AIM'.
VALUE LABELS A_ATYPE
1 'GCE A/AS/A2 level'
2 'GCSE'
3 'GNVQ precursor'
4 'GNVQ/AVCE'
5 'NVQ'
6 'Access to HE'
7 'HNC/HND'
8 'OCN'
9 'Additional NVQ/GNVQ'
10 'Other'
12 ‘Diploma’. |
4. The following SQL code illustrates how to derive A_ATYPE:
|
select
L01, L03, A05
, case when A_LATYPE in ('0001','0002','1413','1430','1431','1432','1433','1434','1435') then 1
when A_LATYPE in ('0003','1400','1422') then 2
when A_LATYPE in ('0017','0028','0029','0030','0886','1423') then 3
when A_LATYPE in ('0035','1414','1415','1416','1417','1418') then 4
when A_LATYPE in ('1326','0036') then 5
when A_LATYPE in ('1440','0004','0005','0007') then 6
when A_LATYPE in ('0031','0032') then 7
when A_LATYPE in ('1442','1443') then 12
when A.A09 like '__CN____' or A.A09 like '___CN___' or A.A09 like 'CN______' or A.A09 like 'ZCAC____' or A.A09 like 'XC______' or A.A09 like 'XBS_____' then 8
when A.A09 like 'X9V_____' or A.A09 like 'Z9V_____' then 9
else 10 end as A_ATYPE
from
ILR0910_E_AIMS A
left outer join
LAD_0910 B
on A.A09 = B.A09
group by
L01, L03, A05, A_LATYPE, A.A09 |
L_ATYPE
5. The following table shows the steps required to derive L_ATYPE
| Step |
Condition |
Action if true |
Action if false |
| 1 |
Does A_ACTIVE=1 |
Go to 2 |
|
| 2 |
Does A_NOTIONLEV=9 |
Set A_NOTIONLEV=0. Go to 3 |
Go to 4 |
| 3 |
(Aggregation)For all aims where the Learner (L03) and Provider (L01) are the same. |
A_NOTIONLEV is the maximum level of the aims in A_NOTIONLEV. Go to 4. |
|
| 4 |
Does L_NOTIONLEV=A_NOTIONLEV. |
Go to 5. |
Ignore those cases. |
| 5 |
(Aggregation)For all aims where the Learner (L03) and Provider (L01) are the same. |
MAX_ACTUAL_GLH is the maximum GLH of the aims in A_INYR_ACTUAL_GLH. Go to 6. |
|
| 6 |
Does MAX_ACTUAL_GLH=
A_INYR_ACTUAL_GLH. |
Go to 7. |
Ignore those cases. |
| 7 |
(Aggregation)For all aims where the Learner (L03) and Provider (L01) are the same. |
MIN_A_ATYPE is the minimum GLH of the aims in A_ATYPE and MAX_A_ATYPE is the maximum GLH of the aims in A_ATYPE. Go to 8. |
|
| 8 |
Does MIN_A_TYPE=MAX_A_TYPE |
L_ATYPE=MIN_A_TYPE |
L_ATYPE=10. |
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does L_ATYPE contain a value |
None |
Set L_ATYPE = -1 |
| 2 |
Does L_ACTIVE=0 |
Set L_ATYPE=-1 |
None |
6. The following SPSS code illustrates how to derive A_ATYPE, L_ATYPE.
|
GET FILE ‘….aims file’.
/*Include Active aims only.
SELECT IF A_ACTIVE=1.
/*Set value 9 in A_NVQLEV to -0.5
RECODE A_NOTIONLEV (9=-0.5).
/*Aggregate to learner/provider level taking the maximum NVQ level against all of the aims.
AGGREGATE OUTFILE *
MODE=ADDVAR
/BREA=L01 L03
/L_NOTIONLEV=MAX(A_NOTIONLEV).
/*Select those records where L_NOTIONLEV is the same as A_NOTIONLEV.
SELECT IF L_NOTIONLEV=A_NOTIONLEV.
/*Aggregate to learner/provider level taking the maximum GLH against all of the aims.
AGGREGATE OUTFILE *
/BREAK=L01 L03
/MAX_ACTUAL_GLH=max(A_INYR_ACTUAL_GLH).
/*Select those records where MAX_ACTUAL_GLH is the same as A_INYR_ACTUAL_GLH.
SELECT IF (MAX_ACTUAL_GLH=A_INYR_ACTUAL_GLH).
/*Aggregate to learner/provider level to calculate maximum and minimum Aim type.
AGGREGATE OUTFILE *
/BREAK=L01 L03
/Min_A_TYPE=MIN(A_ATYPE)
/Max_A_TYPE=MAX(A_ATYPE).
/*Where the Minimum Aim Type and Maximum Aim Type is the same, L_ATYPE is the Minimum Aim type./*Otherwise L_ATYPE is set to 10 - Other.
DO IF Min_A_TYPE=Max_A_TYPE.
COMPUTE L_ATYPE=Min_A_TYPE.
ELSE.
COMPUTE L_ATYPE=10.
END IF.
SAV OUT ‘…….\l_notionlev.sav’ /KEEP L01 L03 L_NOTIONLEV L_ATYPE.
GET FILE ‘…………Learner data set’.
SORT CASES L01 L03.
MATCH FILE
/FILE= *
/TABLE=’…… L_NOTIONLEV.SAV’
/BY L01 L03.
RECODE L_NOTIONLEV (-0.5=9).
RECODE L_NOTIONLEV(SYSMIS=-1). |
7. The following SQL code illustrates how to derive L_ATYPE:
|
-- Execute part 1 first then part 2
-- part 1
select
T2.L01, T2.L03
, case when max(B.A_ATYPE)=min(B.A_ATYPE) then min(B.A_ATYPE) else 10 end as L_ATYPE
into #temp1
from
(
select
L01, L03, count(A05) as aims
, max(A_NOTIONLEV) as A_NOTIONLEV
, case when max(A_NOTIONLEV)=L_NOTIONLEV then max(A_INYR_ACTUAL_GLH) end as MAX_ACTUAL_GLH
from
(
select
L01, L03, A05
, case when A_NOTIONLEV=9 then 0 else A_NOTIONLEV end as A_NOTIONLEV
, A_INYR_ACTUAL_GLH, L_NOTIONLEV
from
RC_Test_Aims
where A_ACTIVE=1
)T
group by
L01, L03, L_NOTIONLEV
)T2
join
RC_Test_Aims B
on T2.L01=B.L01 and T2.L03=B.L03
where T2.MAX_ACTUAL_GLH=B.A_INYR_ACTUAL_GLH
group by
T2.L01, T2.L03
-- part 2
select
A.L01, A.L03, count(A.A05) as aims
, case when B.L_ATYPE is null then -1 else B.L_ATYPE end as L_ATYPE
from
RC_Test_Aims A
join
#temp1 B
on A.L01=B.L01 and A.L03=B.L03
group by
A.L01, A.L03, B.L_ATYPE |
Date last modfied: 10th December 2009
