Im Blog meines Namensvetter Lars Schreiber, einen ausgewiesenen Power BI Experten fand ich einen Beitrag zur automatischen Ermittlung aller deutschen Feiertag je Bundesland (https://ssbi-blog.de/blog/business-topics/automatisch-alle-deutschen-feiertage-je-bundesland-ermitteln/).
Da ich in einem Projekt für die Ermittlung der Techniker-Auslastung die Netto-Arbeitstage pro Jahr benötigte, adaptierte ich diesen Lösungsansatz für Qlik.
Datenquelle bildet die Website www.arbeitstage.org für die zunächst eine Dataconnection [lib://www_Arbeitstage.org] und Inhalt https://www.arbeitstage.org/ muss angelegt wurde.
Dann die Definition einiger Variablen
SET vRoot = 'https://www.arbeitstage.org/';
SET vStartJahr = '2012';
LET vEndJahr = YEAR(TODAY())+1;
Eine Inline-Tabelle nimmt die Bundesländer auf, genau in der Schreibweise der Web-Sub-URLs:
Bundesländer:
LOAD * INLINE [
Bundesland
baden-wuerttemberg,
bayern,
berlin,
brandenburg,
bremen,
hamburg,
hessen,
mecklenburg-vorpommern,
niedersachsen,
nordrhein-westfalen,
rheinland-pfalz,
saarland,
sachsen,
sachsen-anhalt,
schleswig-holstein,
thueringen
];
Danach erfolgt die Generierung einer Tabelle mit den URLs für jedes Bundesland
// äußere Schleife für alle Bundesländer
FOR l= 0 TO NOOFROWS('Bundesländer')-1
LET vCurrentBL = PEEK('Bundesland',$(l),'Bundesländer');
//innere Schleife für die Jahre
FOR j= $(vStartJahr) TO '$(vEndJahr)'
URLTabelle:
LOAD
'$(vCurrentBL)' as Bundesland,
'$(j)' as Jahr,
'$(vRoot)$(vCurrentBL)/feiertage-$(j)-$(vCurrentBL)/' as url
AUTOGENERATE 1;
NEXT j
NEXT l
Mittels dieser Tabelle kann dann auf die einzelnen Webseiten dynamisch zugegriffen werden. Wichtig ist hier der Parameter (url is [$(vCurrentURL),…], welcher innerhalb der Schleife jetzt die abzufragende URL pro Bundesland aus der zuvor erstellten Tabelle verwendet.
//--------------------------------------------------------------------------------------
// Zugriff auf die Website mit dynamisch veränderlicher URL
// und Erzeugung einer Tabelle mit allen Feiertagen aller Bundesländer gewünschter Jahre
//--------------------------------------------------------------------------------------
FOR i = 0 TO NOOFROWS('URLTabelle')-1
LET vCurrentCountry = PEEK('Bundesland',$(i),'URLTabelle');
LET vCurrentYear = PEEK('Jahr',$(i),'URLTabelle');
LET vCurrentURL = PEEK('url',$(i),'URLTabelle');
Feiertage:
LOAD
'$(vCurrentCountry)' as Bundesland,
'$(vCurrentYear)' as Jahr,
[Feiertag] as Feiertagbezeichnung,
DATE(MAKEDATE(RIGHT([Datum],4),MID([Datum],8,2),MID([Datum],5,2)),'DD.MM.YYYY') as Feiertagdatum
FROM [lib://www_arbeitstage.org]
(url is [$(vCurrentURL)],html, codepage is 1252, embedded labels, table is @1);
NEXT i
DROP TABLES Bundesländer,URLTabelle; //temporäre Tabellen werden nicht mehr benötigt
Zu guter Letzt erfolgt die Aufbereitung der Feiertage als String:
//--------------------------------------------------------------------------------------
// Aufbereitung als String
// zur Verwendung in MATCH-Funktion oder als String der Feiertag in NETWORKDAYS
// hier für Baden-Württemberg
//--------------------------------------------------------------------------------------
Feiertag_String:
LOAD
CHR(39)&CONCAT(Feiertagdatum,CHR(39)&','&CHR(39))&CHR(39) as Feiertag_String
RESIDENT Feiertage
WHERE MATCH(Bundesland,'baden-wuerttemberg')
//AND Jahr >= ?
;
LET vFeiertage= PEEK('Feiertag_String',0,'Feiertag_String');
Wozu das ?
Damit kann dann im generierten Kalender das Flag für ein Arbeitstag oder Feiertag pro Kalenderdatum ermittelt werden:
// Arbeitstage im Jahr
IF(WEEKDAY(%Kal_DatumID)<5
AND NOT MATCH(DATE(%Kal_DatumID),$(vFeiertage)),1,0) as _Kal_ArbeitstagKnz,
IF(MATCH(DATE(%Kal_DatumID),$(vFeiertage)),1,0) as _Kal_FeiertagKnz,
Zugegeben 100% ist die Lösung nicht, da es z.B. auch städtebezogene Feiertage gibt, wie z.B. in Berlin oder Augsburg.