Learner Level Delivery Location Postcode Data Definition 2009/2010

L_A23

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. Postcode of the Learner's Delivery Provider.

PURPOSE

2. Identifies the delivery location postcode of the learner in order to generate learner delivery location areas.

RELEVANT COLLECTIONS

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

SOURCE DATA

3. The following variables are used as source data for the calculation of Learner Level Delivery Location Postcode.

Field Name	Label	Dataset
A10	A10 LSC Funding stream	Aims Standard File
A23	A23 Delivery Location Postcode	Aims Standard File
A27	A27 Learning Start Date	Aims Standard File
A_INYR_ ACTUAL_GLH	A_INYR_ACTUAL_GLH Aim Actual Current Year GLH	Aims Standard File
A_NOTIONLEV	A_NOTIONLEV Notional Level of the Aim	Aims Standard File
A04	A04 Data set identifier code	Aims Standard File

DERIVED VARIABLES AND DATASETS
4. The definition produces the following derived variable(s)

Field Name	Label	Dataset
L_A23	L_A23 Learner Level Delivery Location Postcode	Learner Standard File

DETAILED DEFINITION

5. For LR, the Learner Level Delivery Location Postcode (L_A23) is derived using the Delivery Location Postcode of the aim (A23) with the highest Notional Level (A_NOTIONLEV). If there is more that one aim with that NVQ level then the aim with the highest total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) is used. If there is more than one Delivery Location Postcode (A23) with the same total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) then the aim with the latest Learning Start Date (A27) is used to provide the Learner Level Delivery Location Postcode (L_A23).

6. For ER, the aims that are used for deriving Postcode of the Learners Delivery Provider (L_A23) are those Programme aims (A04=35) that are active in the current academic year (A_ACTIVE=1), and are not an apprenticeship (A15 = 2, 3 or 10), and are ER funded (A10=45), ER main funded aim (A10=46) LSC ESF co-financed (A10=70), other LSC funding stream (A10=80) or Non LSC funded (A10=99). Of these, the aim with the latest Learning Start Date (A27) is used to provide the Learner Level Delivery Location Postcode (L_A23).

Learner Level Delivery Location Postcode Sample Code

1. The following SPSS code is provided to illustrate the Learner Level Delivery Location Postcode data definition.

L_A23

ER & ESF SR

2. The following table shows the steps required to derive L_A23 for LR and ESF SR

Step	Condition	Action if true	Action if false
1	Does A_ACTIVE = 1 and A04=35 OR (A10=45, 46, 70, 80 or 99) AND A15 does not = 2,3, or 10	Go to 2.
2	(Aggregation) For all aims where the Learner (L03) and provider (L01) are the same, does the Aim have the latest start date (A27) of all aims for the learner.	Set L_A23=A23

Step	Condition	Action/Action if true	Action if false
1	Does L_A23 contain a value	None	Set L_A23 = NA

3. The following SPSS code illustrates how to derive L_A23 for ER & ESF SR.

/*This is carried out on the aims file.
GET FILE ‘…………AIMS data set’.
/* For WBL the L_A23 belongs to the programme type with the latest start date.
SELECT IF A_ACTIVE=1 and A04=35 OR (ANY(A10,45,46,70,80,99) AND NOT ANY(A15,2,3,10)).
SORT CASES L01 L03 A27.
AGGREGATE OUTFILE ….LA23AGG.sav
 /BREAK=L01 L03
 /L_A23=LAST(A23).
GET FILE ‘…………LEARNER data set’.
SORT CASES L01 L03.
MATCH FILE
/FILE= *
/TABLE=’……LA23AGG.SAV’
/BY L01 L03.
IF LA23=”” LA23=’NA’.
SAV OUT ‘…….L_A23.sav’ /KEEP L01 L03 L_A23.

4. The following SQL code illustrates how to derive L_A23 for ER & ESF SR:

-- part 1

select

L01, L03, count(A05) as aims
, max(A27) as M_A27

into #temp1

from

RC_Test_Aims

where
A_ACTIVE=1 and (A04=35 or
(A10 in (45,46,70,80,99) and A15 not in (2,3,10)))

group by

L01, L03

-- part 2

select

B.L01, B.L03
, A23 as L_A23

into #temp2

from

#temp1 A

join

RC_Test_Aims B

on A.L01=B.L01 and A.L03=B.L03

where
M_A27=A27

-- part 3

select

A.L01, A.L03
, case when B.L_A23='' then 'NA' else B.L_A23 end as L_A23

from

RC_Test_Learner A

join

#temp2 B

on A.L01=B.L01 and A.L03=B.L03

LR, UfI & ASL

For LR, the Learner Level Delivery Location Postcode (L_A23) is derived using the Delivery Location Postcode of the aim (A23) with the highest Notional Level (A_NOTIONLEV).

If there is more that one aim with that notional level then the aim with the highest total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) is used.

If there is more than one Delivery Location Postcode (A23) with the same total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH) then the aim with the latest Learning Start Date (A27) is used to provide the Learner Level Delivery Location Postcode (L_A23).

1. The following table shows the steps required to derive L_A23 for LR, UfI & ASL

Step	Condition	Action/Action if true	Action if false
1	Does A_ACTIVE =1	Go to 2.
2	(Aggregation) For all aims where the learner (L03) and provider (L01) are the same. Do the aims have the highest Notional Level (A_NOTIONLEV), learner.	Go to 3.
3	Do the aims have the same value in A_23	Set L_A23=A23	Go to 4
4	Do the aims have the highest total Actual Current Year Guided Learning Hours (A_INYR_ACTUAL_GLH)	Go to 5
5	Do the aims have the same value in A_23	Set L_A23=A23	Go to 6
6	(Aggregation) For all aims where the Learner (L03) and provider (L01) are the same. does the Aim have the latest start date (A27) of all aims for the learner	Set L_A23=A23

Step	Condition	Action/Action if true	Action if false
1	Does L_A23 contain a value	None	Set L_A23 = NA

2. The following SPSS code illustrates how to derive L_A23 for LR, UfI & ASL.

GET FILE ………….AIMS.SAV'.
/* For LR L_A23 belongs to the aim with the highest Notional, level. Where there is more than one aim with the same highest Notional level and different delivery provider postcodes, we take into account the actual cyglh associated with each postcode.
/* Exclude those aims which are not included for analytical purposes.
SELECT IF A_ACTIVE=1.
RECODE A_NIOTIONLEV (9=-0.5).
AGGREGATE OUTFILE *
  MODE=ADDVAR
  /BREA=L01 L03 
  /MAX_NOTION=MAX(A_NOTIONLEV).
SELECT IF MAX_NOTION=A_NOTIONLEV.
AGGREGATE OUTFILE *
  MODE=ADDVAR
  /BREA=L01 L03 
  /MAX_ACTUAL_GLH=max(A_INYR_ACTUAL_GLH).
SELECT IF (MAX_ACTUAL_GLH=A_INYR_ACTUAL_GLH).
SORT CASES L01 L03 A27.
AGGREGATE OUTFILE *
MODE=ADDVAR
/BREAK=L01 L03
/L_A23_LATEST_START=LAST(A23).
STRING L_A23 (A8).
COMPUTE L_A23=L_A23_LATEST_START.
IF MISSING(L_A23) L_A23=’NA’.
SAV OUT ‘…….L_A23.sav’ /KEEP L01 L03 L_A23.

3. The following SQL code illustrates how to derive L_A23 for LR, UfI & ASL:

--Executte part by part in order

--Part 1

SELECT
       L01
       , L03
       , A27
       , Max(A_NVQLEV) A_NVQLEV

     INTO #AiMS

FROM

     LSC_MI_db_pub.dbo.ILR0809_L04_AIMS

GROUP BY
     L01
       , L03
       , A27

--Part 2

SELECT

     LA.L01
       , LA.L03
       , LA.A27
       , La.A_NVQLEV
       , Max(A_INYR_EXPECTED_GLH) A_INYR_EXPECTED_GLH

     INTO #GLH

FROM

     LSC_MI_db_pub.dbo.ILR0809_L04_AIMS LA
       JOIN
     #AiMS A

          ON A.L01 = LA.L01
           AND A.L03 = LA.L03
          AND A.A27 = LA.A27
           AND A.A_NVQLEV = LA.A_NVQLEV

GROUP BY
      LA.L01
       , LA.L03
       , LA.A27
       , La.A_NVQLEV

--Part 3

SELECT

     LA.L01
       , LA.L03
       , LA.A23 as L_A23

FROM

       LSC_MI_db_pub.dbo.ILR0809_L04_AIMS LA
       JOIN
       #GLH A

       ON A.L01 = LA.L01
          AND A.L03 = LA.L03
          AND A.A27 = LA.A27
          AND A.A_NVQLEV = LA.A_NVQLEV
          AND A.A_INYR_EXPECTED_GLH = LA.A_INYR_EXPECTED_GLH

GROUP BY
          LA.L01
            , LA.L03
            , LA.A23

Date last modified: 10th December 2009