Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
mattphillip
Creator II
Creator II

Checking whether a student has re-registered

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.

Labels (4)
1 Reply
Rohan
Specialist
Specialist

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.