Personell data - employees adopted

datanibbler — Mon, 26 Jan 2026 18:19:17 GMT

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
;

Re: Personell data - employees adopted

gainkarthi — Fri, 06 Sep 2013 06:03:44 GMT

Can you send the QVW file.

Re: Personell data - employees adopted

datanibbler — Fri, 06 Sep 2013 07:12:05 GMT

Hi Karthikeyan,

no, I'm sorry - it is an HR Dashboard, and personell_data is extremely sensitive. I don't feel too comfortable dealing with it at all as long as I don't have a written explanation of it all, but I can surely not pass it on. Sorry!

I understand that makes it quite difficult for you to help me. Unfortunately that can't be helped.

All I wanted, actually, was to check the logic of the script. I have already checked it and I cannot find anything weird. We are a bit stuck with this. If the "error" lies with the data, then it's clear that there is something we haven't understood and that we need a consultant - but as that is a bit complicated at the time being, I wanted to be absolutely sure there was no error in my script.

If there is anything unclear in the script and comments as I posted them, let me know and I will do my best to clarify.

Thanks a lot!

Best regards,

DataNibbler

topic Personell data - employees adopted in QlikView

Personell data - employees adopted

Re: Personell data - employees adopted

Re: Personell data - employees adopted