Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.