Hi,
I just talked to a contact person from HR in my company:
I have built a Dashboard displaying various values concerning our personell, among them the employees adopted (who began as contract workers and became permanent employees eventually).
I will post here the script code I currently use.
Needless to say, the result is wrong - that does not necessarily mean my calculation is wrong, but I'd like to be sure about that.
The thing is quite complex, with a series of RESIDENT LOADs and JOINs - so an error somewhere is quite possible.
Could anybody please have a look at this and tell me whether something meets your eye as wrong?
Thanks a lot!
Best regards,
DataNibbler
// First comes a general LOAD from the database of a load of data concerning personell (all the employees who were
// with the company until three months back at least);
MA_Grunddaten:
LOAD
pnr as Pers_Nr4,
TRIM(vname) & TRIM(nname) as Name,
makz as Kennz,
WHERE ((RIGHT(datumbis, 2) & '.' & MID(datumbis, 5, 2) & '.' & LEFT(datumbis, 4)) >= Monthstart(TODAY(), -3));
// Then I add a counter to that base_table - when an employee is adopted, the name stays the same,
// but he is assigned another personell_number.
LEFT JOIN (MA_Grunddaten)
LOAD
Name,
COUNT(Pers_Nr4) as ID_count
// COUNT(Kennz) as Kennz_count
RESIDENT MA_Grunddaten
GROUP BY Name
;
Übernahmen_pre:
LOAD
Pers_Nr4,
Name,
Kennz,
Eintritt
RESIDENT MA_Grunddaten
WHERE ID_count > 1
;
// Every employee now has two "join_dates" ("Eintritt"): The date the employee first joined the company
// AND the date he/she was adopted. I need only the latter.
LEFT JOIN (Übernahmen_pre)
LOAD
Name,
DATE(max(Eintritt)) as Datum_Übernahme
Resident Übernahmen_pre
GROUP BY Name
;
// Next, I want to make sure I grab only employees who have two records with two different type_flags - usually, when
// a contract worker is adopted, his/her type_flag changes, but there are exceptions.
Übernahmen_pre2:
LOAD
Name,
COUNT(DISTINCT Kennz) as Kennz_count
RESIDENT Übernahmen_pre
GROUP BY Name
;
LEFT JOIN (Übernahmen_pre)
LOAD
Name,
Kennz_count
RESIDENT Übernahmen_pre2
;
// Now the actual work is done - only the resulting tables has both records for every employee who was adopted
// (both with the later date, the date of his/her adoption), so we use the MOD() function to grab only every other record.
DROP TABLE Übernahmen_pre2;
Übernahmen_pre3:
LOAD
Name,
RecNo() as Row_ID,
Datum_Übernahme
RESIDENT Übernahmen_pre
WHERE Kennz_count > 1
;
DROP TABLE Übernahmen_pre;
// In dieser Tabelle haben wir im Prinzip schon das richtige, aber wir brauchen nur jeden zweiten Datensatz.
// Deshalb nehmen wir mithilfe der MOD-Fkt nur die geraden Datensätze raus (jew. den zweiten).
Übernahmen:
LOAD
Name,
Datum_Übernahme,
Datum_Übernahme as %Datum,
Month(Datum_Übernahme) as Übernahmemonat,
Year(Datum_Übernahme) as Übernahmejahr
RESIDENT Übernahmen_pre3
WHERE Mod(Row_ID, 2)=0
;
