Checking whether a student has re-registered

[mattphillip]

I'm hoping someone might be able to help. We have a database of student records which exports weekly and includes a date field. The database also includes a Student ID field and 'Occurrence' field which is an alphanumeric label which identifies intake points for students to re-register. What we'd like to do is find a way to check whether a student who is currently registered e.g Occurrence value = 'E1', date = '01/09/2023', has re-registered in a subsequent export e.g. Occurrence value = 'E2', date = '01/02/2024'

We used to to this manually with vlookups in excel but we'd like to automate this if possible, with the check value added to a new field called 'Re-Registered' = 'Y/N'.

To complicate matters slightly, certain students can take a study break for one intake, as such we'd need to be able to check whether a student from 'E1' may have re-registered in 'E3' for example.

Any help would be most appreciated.

Thank you.

[Rohan]

Hi Matt,

Try:

Datecheckmap:

mapping load 

StudentID&'|'&IntakeID as Key,

Date;

Load 

StudentID,

IntakeID,

min(Date) as Date

From Table group by  StudentID, IntakeID;

Table :

Load *,

if(Date>first_intake_Date,'Re-register','New Intake') as Flag;

Load *,

ApplyMap('Datecheckmap',StudentID&'|'&IntakeID,'N/A') as first_intake_Date

From Table;

Regards,

Rohan.