Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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...
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