Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
iczkla
Contributor III
Contributor III

Loop through periods to identify all min and max strings

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.

 

 

Labels (3)
1 Reply
MarcoWedel

Hi,

maybe one solution could be:

MarcoWedel_0-1656175638996.png

 

MarcoWedel_1-1656175650185.png

 

MarcoWedel_2-1656175669764.png

 

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