Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 this by using below statement in the script:
HC:
LOAD
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
2,APAC,2022-02
3,APAC,2022-02
4,APAC,2022-02
5,APAC,2022-02
7,EMEA,2022-02
8,EMEA,2022-02
9,EMEA,2022-02
11,EMEA,2022-02
12,EMEA,2022-02
1,APAC,2022-03
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
2,APAC,2022-04
3,APAC,2022-04
4,APAC,2022-04
6,EMEA,2022-04
7,EMEA,2022-04
8,EMEA,2022-04
9,EMEA,2022-04
11,EMEA,2022-04
12,EMEA,2022-04
];
TermsTMP:
LOAD
EMPID,
maxstring([Snapshot]) as [Snapshot],
'Last active month' as [Last Snapshot Month]
Resident HC
Group by EMPID;
HiresTMP:
LOAD
EMPID,
minstring([Snapshot]) as [Snapshot],
'First active month' as [First Snapshot Month]
Resident HC
Group by EMPID;
Overall this works, however only to identify first occuring Employee ID within entire table, so if someone was terminated and then rehired it will not be flagged as hire. I was wondering if it would be possible to create some kind of a loop that would iterate through all Snapshots and compare them to each other: first to second, second to third, third to fourth and so on to make sure all hires are captured.
Hi,
maybe one solution could be:
HCtemp:
LOAD EMPID,
Region,
Date#(Snapshot,'YYYY-MM') as Snapshot
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
2,APAC,2022-02
3,APAC,2022-02
4,APAC,2022-02
5,APAC,2022-02
7,EMEA,2022-02
8,EMEA,2022-02
11,EMEA,2022-02
12,EMEA,2022-02
1,APAC,2022-03
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
2,APAC,2022-04
3,APAC,2022-04
4,APAC,2022-04
6,EMEA,2022-04
7,EMEA,2022-04
8,EMEA,2022-04
11,EMEA,2022-04
12,EMEA,2022-04
13,EMEA,2022-04
1,APAC,2022-05
2,APAC,2022-05
3,APAC,2022-05
4,APAC,2022-05
5,APAC,2022-05
6,EMEA,2022-05
7,EMEA,2022-05
8,EMEA,2022-05
9,EMEA,2022-05
11,EMEA,2022-05
12,EMEA,2022-05
13,EMEA,2022-05
1,APAC,2022-06
2,APAC,2022-06
3,APAC,2022-06
4,APAC,2022-06
5,APAC,2022-06
6,EMEA,2022-06
7,EMEA,2022-06
8,EMEA,2022-06
11,EMEA,2022-06
12,EMEA,2022-06
13,EMEA,2022-06
1,APAC,2022-07
2,APAC,2022-07
3,APAC,2022-07
4,APAC,2022-07
5,APAC,2022-07
6,EMEA,2022-07
7,EMEA,2022-07
8,EMEA,2022-07
9,EMEA,2022-07
11,EMEA,2022-07
12,EMEA,2022-07
13,EMEA,2022-07
];
HCtemp2:
LOAD *,
If(EMPID=Previous(EMPID),Peek(EmploymentNo)+IsFirstMonth,1) as EmploymentNo;
LOAD *,
-(EMPID<>Previous(EMPID) or Snapshot>AddMonths(Previous(Snapshot),1)) as IsFirstMonth
Resident HCtemp
Order By EMPID, Snapshot;
HC:
LOAD *,
-(EMPID<>Previous(EMPID) or Previous(IsFirstMonth)) as IsLastMonth
Resident HCtemp2
Order By EMPID, Snapshot desc;
DROP Tables HCtemp, HCtemp2;
hope this helps
Marco