2 Replies Latest reply: Sep 6, 2013 3:25 AM by Friedrich Hofmann RSS

    Personell data - employees adopted

    Friedrich Hofmann

      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
          Karthikeyan Subramaniam

          Can you send the QVW file.

            • Re: Personell data - employees adopted
              Friedrich Hofmann

              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