Match Dates

[dmoreno2605]

Hi everyone!!  I have the following table

Employee	Date	Code
5555	10/10/2018	H
5555	27/06/2018	T
5555	23/06/2018	H
5555	06/04/2018	T
5555	03/03/2018	H

This is an employee, H=Hire and T=Term, this is an employee that was hired 3 times and he left 2 times, so he is currently active, i want to match Date, i need to get the following 

Employee	HireDate	TermDate
5555	10/10/2018	null/empty
5555	23/06/2018	27/06/2018
5555	03/03/2018	06/04/2018

Any ideas?

Thanks!

[sunny_talwar]

This how you want the data to be stored in the backend or do you want to show this on the front end of the app?

[dmoreno2605]

This is how i want it in the back

[sunny_talwar · Accepted solution]

Try something like this

Table:
LOAD * INLINE [
    Employee, Date, Code
    5555, 10/10/2018, H
    5555, 27/06/2018, T
    5555, 23/06/2018, H
    5555, 06/04/2018, T
    5555, 03/03/2018, H
];

TempTable:
LOAD *,
	 If(Employee = Previous(Employee), If(Code = 'T', Peek('Times'), RangeSum(Peek('Times'), 1)), 1) as Times
Resident Table
Order By Employee, Date;

DROP Table Table;

FinalTable:
LOAD Employee,
	 Times,
	 Date as HireDate
Resident TempTable
Where Code = 'H';

Left Join (FinalTable)
LOAD Employee,
	 Times,
	 Date as TermDate
Resident TempTable
Where Code = 'T';

DROP Table TempTable;

[dmoreno2605]

It totally did the job, thanks!!!