topic Re: Match Dates in App Development

Match Dates

dmoreno2605 — Mon, 20 Dec 2021 21:38:40 GMT

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!

Re: Match Dates

sunny_talwar — Thu, 13 Dec 2018 18:09:18 GMT

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?

Re: Match Dates

dmoreno2605 — Thu, 13 Dec 2018 18:10:41 GMT

This is how i want it in the back

Re: Match Dates

sunny_talwar — Thu, 13 Dec 2018 18:17:53 GMT

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;

Re: Match Dates

dmoreno2605 — Thu, 13 Dec 2018 18:34:35 GMT

It totally did the job, thanks!!!