i have a below data file from the 2018-12 where the status field should show as
Joiner if emp id is not present in previous month.
no change if emp is present in previous month and current month
leaver - if employee is not present in current month.
i am able to get the joiner and no change using the below logic
IF("employeeID" = Peek("Employee ID") , 'NO CHANGE',IF("Employee ID" <> Peek("Employee ID"),'JOINER', 'NA')) AS STATUS
but unable to get the leaver status.
Month, empid, ename
2017 - 01, 11, abc
2018 - 02, 11, abc
2018 -03, 11, abc
2018 - 04, 11, abc
2018 -06, 22, xyz
2018 - 07, 22, xyz
2018 -08, 22, xyz
2018 -09, 22 , xyz
2018 -11, 22, xyz
2018 -12, 22, xyz
output should be:
Month, empid, ename, status
2017 - 01, 11, abc , joiner
2018 - 02, 11, abc, nochange
2018 -03, 11, abc, nochange
2018 - 04, 11, abc, leaver
2018 -06, 22, xyz, joiner
2018 - 07, 22, xyz, no change
2018 -08, 22, xyz, nochange
2018 -09, 22 , xyz, leaver
2018 -11, 22, xyz, joiner
2018 -12, 22, xyz, nochange.
if i use above logic : for 2018 - 04, 11, abc record i am getting data as nochange. But i need to get the status as leaver. As the data file is from 2018-12 month.
Can some one suggest a solution to this.
Thanks in Advance.
