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