Formula problem in script - only one value left? - Qlik Community

datanibbler · ‎2013-11-15

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...

datanibbler · ‎2013-11-15

Hi,

I am one step further toward finding the error now:

-> I have just tried just deactivating that EXISTS_clause - and phew, there are a truckload of different leave_dates <=> when I re-activate it, phew - there's just one.

I don't know why that might be, however, the underlying list hasn't changed - or, I hope it hasn't, let's see... people keep changing underlying lists which we have built QlikView_apps on just to keep us busy...

datanibbler · ‎2013-11-15

Hi,

don't bother: I just noticed what it is. Seems I stumbled over yet another very strange behaviour in our database - nothing wrong with my script. I'll try to talk to a contact person from HR on Monday.

Best regards,

DataNibbler