How to Get This ....

[alvinford]

Hi All,

How to implement the following logic.

I have the following data

StudentCode	StudentID	JoinDate	StudentPramotedDate	NewStudentID
86	13575	01/08/2010	01/06/2011	13772
86	13772	01/06/2011	01/06/2011	13805
86	13805	01/06/2011
86	13936	01/07/2012	01/10/2012	14054
86	14054	01/10/2012

In the above table for some of the StudentID (Ex 13575)  have NewStudentID (Ex 13772). NewStudentID is also in the list of StudentID (Ex 13722).

My requirement is whenever there is NewStudentID for the StudentID . The NewStudentID JoinDate should be replaced with the OldStudentDate based on StudentPromoDate.

Desired results will be as below.(You can check the JoinDate)

StudentCode	StudentID	JoinDate	StudentPramotedDate	NewStudentID
86	13575	01/08/2010	01/06/2011	13772
86	13772	01/08/2010	01/06/2011	13805
86	13805	01/08/2010
86	13936	01/07/2012	01/10/2012	14054
86	14054	01/07/2012

Regards,

Alvin.

[jonathandienst]

Hi

Use something like this after loading your raw data:

Join(Data)

LOAD StudentCode,

     Date(Min(JoinDate)) As JoinDate2

Resident Data

Group By StudentCode

;

The updated Join Date is in JoinDate2. See the attached.

Hope that helps

Jonathan

[alvinford]

Hi Jonathan,

Thanks for your reply.

I cant take the Min(JoinDate) as I cannot group it by StudentCode . For the same studentCode the JoinDate has to be affected for different StudentID like 13805 and 14054.

If I take the Min(JoinDate) based on the StudentCode for all records my value will be 01/08/2010.

Regards,

Alvin.

[jonathandienst]

So the student codes are not distinct for the students?

[alvinford]

Hi Jonathan,

The Student Codes are distinct but the values which I want to achieve are based on the StudentID.

Regards,

Alvin.