Identify removed records

[iczkla]

Hello,

I have an app into which each month I am loading new Headcount file. Each employee is identified by unique ID and files are segregated by report period derived from title in format YYYY-MM.

I want to create a table in which each month I will be able to identify new and terminated employees.

So far I managed to create solution for identifying new employees by using below statement in the script:

if(previous([EMP ID])<>[EMP ID],'Yes','No') as "New Hire",

however I am struggling to create a statement which will allow me to identify terminations (ID's which were existing within previous period but are not existing in the new one). 

Any suggestions on how to achieve this will be much appreciated.

[vinieme12]

please post some sample data

[iczkla]

Hi Vineeth,

Data looks like below:

EMP ID	Region	Snapshot
1	APAC	2022-01
2	APAC	2022-01
3	APAC	2022-01
4	APAC	2022-01
5	APAC	2022-01
6	EMEA	2022-01
7	EMEA	2022-01
8	EMEA	2022-01
9	EMEA	2022-01
10	EMEA	2022-01
1	APAC	2022-02
2	APAC	2022-02
3	APAC	2022-02
4	APAC	2022-02
5	APAC	2022-02
6	EMEA	2022-02
7	EMEA	2022-02
8	EMEA	2022-02
9	EMEA	2022-02
11	EMEA	2022-02
12	EMEA	2022-02
2	APAC	2022-03
3	APAC	2022-03
4	APAC	2022-03
6	EMEA	2022-03
7	EMEA	2022-03
8	EMEA	2022-03
9	EMEA	2022-03
11	EMEA	2022-03
12	EMEA	2022-03

[vinieme12 · Accepted solution]

As below;

raw:
LOAD
EMPID
,Region
,date(date#(Snapshot,'YYYY-MM'),'YYYY-MM-DD') as Snapshot
,EMPID&'|'&Region as empKey
,'1' as flag
Inline [
EMPID,Region,Snapshot
1,APAC,2022-01
2,APAC,2022-01
3,APAC,2022-01
4,APAC,2022-01
5,APAC,2022-01
6,EMEA,2022-01
7,EMEA,2022-01
8,EMEA,2022-01
9,EMEA,2022-01
10,EMEA,2022-01
1,APAC,2022-02
2,APAC,2022-02
3,APAC,2022-02
4,APAC,2022-02
5,APAC,2022-02
6,EMEA,2022-02
7,EMEA,2022-02
8,EMEA,2022-02
9,EMEA,2022-02
11,EMEA,2022-02
12,EMEA,2022-02
2,APAC,2022-03
3,APAC,2022-03
4,APAC,2022-03
6,EMEA,2022-03
7,EMEA,2022-03
8,EMEA,2022-03
9,EMEA,2022-03
11,EMEA,2022-03
12,EMEA,2022-03
];

tempAsOf:
Load
empKey
,subfield(empKey,'|',1) as EMPID_2
;
LOAD fieldvalue('empKey',iterno()) as empKey
AUTOGENERATE 1
WHILE len(fieldvalue('empKey',iterno()));

left join(tempAsOf)
LOAD date(fieldvalue('Snapshot',iterno())) as Date2
AUTOGENERATE 1
WHILE len(fieldvalue('Snapshot',iterno()));

Left Join (tempAsOf)
Load empKey,Snapshot as Date2,flag as check Resident raw;

AsOF:
Load
*
,if(EMPID_2=Peek(EMPID_2) and isnull(check),'Terminated','Hired') as status

Resident tempAsOf
Order by empKey,Date2 Asc;
drop table tempAsOf;

Dates:
Load EMPID_2
,Date(min(if(status='Hired',Date2))) as HiredDate
,date(max(if(status='Terminated',Date2))) as TerminatedDate
Resident AsOF
Group by EMPID_2;

AsOF: table to identify removed records

Dates: table to get hiring and termination dates

[iczkla]

Thank you so much this works greatly! One more question - is it possible for the terminated status to be appearing next to the EmpID only for the month in which record was terminated?

For example record 10 disappeared in 2022-02, so terminated status should appear only in row with 2/1/2022 date. This would allow me to count number of terminations within the month.

[vinieme12]

Modify the "status" field to below

,if(EMPID_2=Peek(EMPID_2),if(len(peek('status')) or len(check), null(),'Terminated'),'Hired') as status

[vinieme12]

@iczkla please close the thread by marking a response as solution