In einem Projekt musste ich die An- und Abwesenheiten von Mitarbeitern in einer QlikSense Analyse visualisieren. Als Datenquelle war außer einem Mitarbeiter-Stamm und einer Tabelle mit Abwesenheitseinträgen aber nichts vorhanden.
Daher wurde im ersten Schritt eine Tabelle erzeugt, die pro Mitarbeiter seit seinem Eintritt einen Kalendereintrag erzeugt. Hierzu wurde ein Outer Join zu einem zuvor generierten Kalender verwendet:
//------------------------------------------------------------------------------------------------------ // zu jedem Mitarbeiter und // Tag ab DAtum 01.01.2012 einen Datensatz anlegen Tmp_Mitarbeiter_Datum: LOAD "User_UserId" as %UserID, IF("User_UserId"=1,'01.11.2012', DATE(FLOOR(User_CreatedDate))) as Useranlagedatum FROM [$(vQVDInputPath)/21_sageCRM_Extract/sageCRM_Users.qvd] (qvd);
OUTER JOIN (Tmp_Mitarbeiter_Datum) LOAD %Kal_DatumKey as %Datum, _Kal_ArbeitstagKnz FROM [$(vQVDInputPath)/91_Kalender/Kalender.qvd] (qvd);
Da die Mitarbeiter unterschiedlich lange im Unternehmen sind, musste pro Mitarbeiter individuell ein Startdatum ermittelt werden
Tmp_Mitarbeiter_abAnlagedatum: NOCONCATENATE LOAD %UserID, Useranlagedatum, %Datum, _Kal_ArbeitstagKnz, %UserID&'_'&%Datum as User_Date_KEY, 1 as [#Vertrag_Tagezähler] RESIDENT Tmp_Mitarbeiter_Datum WHERE %Datum >= Useranlagedatum //AND %UserID='34' ;
DROP TABLE Tmp_Mitarbeiter_Datum;
In einer Tabelle standen die Abwesenheitseinträge pro Mitarbeiter und einem Datum oder einem Datumsinterval von/bis zur Verfügung. Diese Abwesenheiten mussten auf jeden Tag pro Mitarbeiter „eingerüttelt“ werden.
Zur Vorbereitung wurden in einer Tabelle die Abwesenheiten eingelesen und in einer temporären Tabelle die Tage mit Arbeitstage-Flag (siehe hierzu diesen Blog-Beitrag)
//Abwesenheitsmeldungen pro Mitarbeiter und von / bis Datum Tmp_Abwesend: LOAD ID, APPLYMAP('Map_AbwTypID2Bez',IDAbwTyp) as Abw_Typbez, IDUser as %UserID, APPLYMAP('Map_AbwTag1IDBez',IDTag1) as Abw_Tagestyp, APPLYMAP('Map_AbwTag1IDFaktor',IDTag1) as Abw_Tagesfaktor, DatumVon, DatumBis, AnzTage FROM [$(vQVDInputPath)/31_RTTools_Extract/RTTools_tblAbwesenheit.qvd] (qvd);
//Kalendertag mit Arbeitstageknz Tmp_Kalender: LOAD %Kal_DatumKey as %Datum, _Kal_ArbeitstagKnz FROM [$(vQVDInputPath)/91_Kalender/Kalender.qvd] (qvd);
Das „Einrütteln“ erfolgte dann mittels IntervalMatch-Funktion. Diese Funktion führt zu einer Mehrung der Datensätze, da für jeden Tag zwischen von und bis ein Datensatz erzeugt wird.
//------------------------------------------------------------------------------------------------------ //Auflösung der Abwesenheiten mit von / bis Datum auf jeden einzelnen Tag mittels Intervalmatch // --> pro User und Abwesenheitstag ein Datensatz wird erzeugt Abwesend_Interval: IntervalMatch (%Datum) LOAD DatumVon, DatumBis RESIDENT Tmp_Abwesend ;
und anschließendem Anfügen der Abwesenheitsdetails bzw. des Merkmals eines Arbeitstages.
//anfügen der Abwesenheitsinformationen INNER JOIN (Abwesend_Interval) LOAD ID, Abw_Typbez, Abw_Tagestyp, Abw_Tagesfaktor, AnzTage, DatumVon, DatumBis, %UserID RESIDENT Tmp_Abwesend;
DROP TABLE Tmp_Abwesend;
//anfügen der Kalenderinformationen LEFT JOIN (Abwesend_Interval) LOAD %Datum, _Kal_ArbeitstagKnz RESIDENT Tmp_Kalender; DROP TABLE Tmp_Kalender;
Im letzten Schritt erfolgt die Zusammenführung in einer finalen Tabelle mit Ermittlung von Kennzahlen für die Abwesenheit, Krankheit
//------------------------------------------------------------------------------------------------------ //finale Erstellung der Abwesenheitstabelle Tmp_Abwesend_final: NOCONCATENATE LOAD DISTINCT ID, Abw_Typbez, Abw_Tagestyp, Abw_Tagesfaktor, AnzTage, %Datum, _Kal_ArbeitstagKnz, DatumVon, DatumBis, %UserID,
%UserID&'_'& %Datum as User_Date_KEY, 1 * Abw_Tagesfaktor * _Kal_ArbeitstagKnz as [#Abw_Tage], $(vStdproAT) * Abw_Tagesfaktor * _Kal_ArbeitstagKnz as [#Abw_Std], IF(MATCH(Abw_Typbez,'Krankheit'),1 * Abw_Tagesfaktor * _Kal_ArbeitstagKnz,0) as [#Krank_Tage], IF(MATCH(Abw_Typbez,'Krankheit'),$(vStdproAT) * Abw_Tagesfaktor * _Kal_ArbeitstagKnz,0) as [#Krank_Std] RESIDENT Abwesend_Interval; DROP TABLE Abwesend_Interval;
Da somit pro Mitarbeiter die Anwesenheit oder seine Abwesenheit pro Tag zur Verfügung steht, kann über beliebige Zeiträume somit die Anwesenheits-/Abwesenheits- oder Krankheitsquote ermittelt und visualisiert werden.