Loop through periods to identify all min and max strings

[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 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.

[MarcoWedel]

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