2 Replies Latest reply: Nov 15, 2013 10:18 AM by Friedrich Hofmann RSS

    Formula problem in script - only one value left?

    Friedrich Hofmann

        Hi,

       

      I have a problem in the script of my HR app. I noticed that the diagram was different from what it had been before - suddenly, no one was displayed as having left the company in any month of this year - good for us, but plain wrong.

      Searching for the reason, I eventually noticed that the binary field "Ausgetreten / left", which should have the values 1 and 0, had only the value 0 left - so no one ever left... a strange kind of company ;-)

       

      I have not, however, been able to actually define the underlying reason - I merely know between which two LOAD statements the error must occur - because in the one, I have a great nr. of leave_dates and in the next, I have only >Dec12, 2100< - the standard value for those who are still with the company.

      The first one is the base load from our database. I will post that here:

       

       

      LOAD
         
      pnr as Pers_Nr4,
          makz as Kennz,
         
      DATE((RIGHT(datumvon, 2) & '.' & MID(datumvon, 5, 2) & '.' & LEFT(datumvon, 4))) as Eintritt,
         
      DATE((RIGHT(datumbis, 2) & '.' & MID(datumbis, 5, 2) & '.' & LEFT(datumbis, 4))) as Austritt,
         
      geschl as Geschlecht,
         
      Year(TODAY()) - LEFT(datumgeb, 4) as Alter,
         
      kst as Kostenstelle,
         
      perseh as Vorgängereinheit
      WHERE ((RIGHT(datumbis, 2) & '.' & MID(datumbis, 5, 2) & '.' & LEFT(datumbis, 4)) >= yearstart(TODAY()))
      ;

      SQL SELECT
           gpetab.finr,
           gpetab.pnr,
           gpetab.geschl,
           gpetab.datumgeb,
           gpetab.datumvon,
           gpetab.datumbis,
           gpetab.makz,
           gpetab.kst,
           phitab.perseh
      FROM gpetab, phitab
        WHERE gpetab.finr = 7
        and   gpetab.finr = phitab.finr
        and   LEFT(phitab.perseh, 2) = '96'
        and   gpetab.pnr = phitab.pnr
      ;

      (this is, as you see, an SQL_query from the database.

      What I do with the date is probably more complicated than required (I could have used date#), but that's how I do it.

      The important point is, here I get all possible leaving_dates.

      (after that, there is a tab in the script where I have deactivated all the code in the run of consolidating some tables).

      Then I load several  mapping tables:

       

       

      MAPPING
      LOAD
          
      PERSEH,
          
      Werk_Anw
      FROM
      [\\rgb1sfs201\Regensburg\Common\03-HR\adicom_Vorgängereinheiten.xls]
      (
      biff, embedded labels, table is [Mapping_Werk$]);

      Map_Ber:
      MAPPING
      LOAD
          
      PERSEH,
          
      Ber_Anw
      FROM
      [\\rgb1sfs201\Regensburg\Common\03-HR\adicom_Vorgängereinheiten.xls]
      (
      biff, embedded labels, table is [Mapping_Ber$]);

      Map_Ber2:
      MAPPING
      LOAD
          
      PERSEH,
          
      Ber2
      FROM
      [\\rgb1sfs201\Regensburg\Common\03-HR\adicom_Vorgängereinheiten.xls]
      (
      biff, embedded labels, table is [Mapping_Ber_übergr$]);


      Then I load, from the same file as the mapping tables, a filtering tables with just those personell_units where I'm supposed to display anything:

       

      LOAD
          
      PERSEH as Vorg,
          
      PERSEH as Vorg_einh
      FROM
      [\\rgb1sfs201\Regensburg\Common\03-HR\adicom_Vorgängereinheiten.xls]
      (
      biff, embedded labels, table is [Gesamtmapping$]);

      Then comes the second important LOAD where somehow the values shrink to just one:


      MA_Grunddaten_#3_pre:
      LOAD
      //    Recno() as Line,
          Pers_Nr4 as Pers_Nr5,
         
      Kennz as Kennz_2,
         
      Geschlecht,
         
      Alter,
         
      Eintritt as Eintritt_2,
         
      Month(Eintritt) as Eintrittsmonat_2,
         
      year(Eintritt) as Eintrittsjahr_2,
      //    IF((Month(Eintritt) = month(DATE(Eintritt + IterNo()-1)) AND year(Eintritt) = year(DATE(Eintritt + IterNo()-1))), 1, 0) as 'neu_eingetreten',
          Austritt as Austritt_2,
         
      Month(Austritt) as Austrittsmonat_2,
         
      year(Austritt) as Austrittsjahr_2,
      //    IF((Month(Austritt) = month(DATE(Eintritt + IterNo()-1)) AND Year(Austritt) = year(DATE(Eintritt + IterNo()-1))), 1, 0) as 'ausgetreten',
          IF(Austritt <= TODAY(), Austritt, TODAY()) as Enddatum_2,
      //    Kostenstelle,
          Vorgängereinheit as Vorg_einh,
         
      Vorgängereinheit as Pers_einh
      RESIDENT MA_Grunddaten
      WHERE (Kennz = '7' OR Kennz = '8')
      and    EXISTS(Vorg, Vorgängereinheit)
      ;

      I don't see anything out of the ordinary in that code - do you? I have no idea what is the reason it doesn't work anymore...