QlikView + Qlik Sense Blog von Heldendaten

In 12 Jahren Qlik kann ich mich nicht erinnern, dass eine neue mathematische Funktion in den Release Notes erwähnt wurde. Mit Qlik Sense Nov 2019 SR3 und dem QlikView 12.50 hat sich das geändert: Willkommen FractileExc() Funktion!

Während es in Qlik schon immer eine median() und eine fractile() Funktion, durften sich Excel User bereits in der Version 2010 über zwei Funktionen verschiedene Quantil-Funktionen QUANTIL.INKL und QUANTIL.EXCL freuen.

Zur Erinnerung: die median Funktion liefert das gleiche wie die fractile(0.5) Funktion. Der Median ist jener Wert, der die höhere Hälfte der Werte von der niedrigeren Hälfte der Werte einer Datenreihe separiert. Je nach Verteilung kann dieser Wert sehr unterschiedlich liegen, und auch von anderen Mittelwerten abweichen.

Quantile für Normalverteilung und chi-Quadrat Verteilung

Warum also eine neue Funktion? Unter dem Titel "Die statischen Excel-Funktionen QUANTIL.INKL und QUANTIL.EXKL" findet sich eine schöne Abhandlung des Bloggers Gerhard Pundt. Die dort benutzte Datenserie, wollen wir in Qlik ebenfalls als Datenquelle nutzen.

FractileExc liefert für das 25% und 75% Quantil andere Werte als die Funktion Fractile

Die Ergebnisse für das 25% und 75% Quantil unterscheiden sich also zwischen der Fractile und der FractileExc Funktion. Qlik folgt den Excel Funktionen QUANTIL.INKL und QUANTIL.EXCL und liefert die gleichen Werte wie das Beispiel im oben genannten Blogpost.

Die Erklärung was FractileExc nun für eine mathematische Funktion ist, liefert das erste Kommentar von spaghettieisblog. Es verweist auf Wolfram Mathworld, wo 8 verschiedene Berechnungsmethoden für Quantile genannt werden.




Demzufolge gilt:

Qlik	Excel	Wolfram
Fractile	QUANTIL.INKL	Q7
FractileExc	QUANTIL.EXCL	Q6 Weibull Quantil

Warum QlikView die Funtion FractileExc nun einige Monate später bekommen hat, beruht wohl auf der Tatsache, dass Qlik Sense und QlikView 12.X die gleiche Engine als Codebasis haben. Ähnlich wie bei der sortable Aggr oder autonumber using Funktion tauchen solche Engine Features mit etwas Versatz auch im folgenden QlikView Release auf.

Hier die FractileExc Funktion in QlikView 12.50

Die .qvf und .qvw findet sich hier.

Wordcloud in Qlik

//load data
[Data]:
LOAD
 [Supportcase],
        //replace line breaks with blanks
 replace([Text],chr(10),' ') as Text 
    
 FROM [lib://data/supportcases.xlsx]
(ooxml, embedded labels, table is Tabelle1);

//Split into Words
TempAllWords:
Load
[Supportcase],
trim(subfield([Text],' ')) as Words
resident [Data];

AllWords:
NoConcatenate
load 
[Supportcase],
upper(Words) as Words,
applymap('IrrelevantWords',upper(Words),'relevant') as Relevanz
where len (Words) > 0;
load
[Supportcase],

(if(match(right(Words,1),'.',',','!','?',';') > 0, left(Words,len(Words)-1),Words)) as Words  //cleanup 
resident TempAllWords;

Was dieser Ansatz nicht macht, sind Volltextsuchen-Features wie Stemming  um alle Wörter auf Ihren Wortstamm zu reduzieren. Hier könnte man die Server Side Extensions von QlikView und Qlik Sense nutzen, und die Wörter noch durch ein Python Skript für Stemming laufen lassen.

Die Wordcloud Darstellung selbst kommt mit dem Qlik Sense Visualization Bundle. Bitte auf die Sortierung und die Design|Orientation achten.

Word Cloud nach Häufigkeit sortieren

• Sentiment Connector 
• Watson Natural Language Connector

Qlik vergisst gerade gerne neue Funktionen in den Release Notes zu erwähnen. Barry Harmsen hat gerade das neue Autonumber Script Statement beschrieben. Die beiden Chart Funktionen getObjectDimension und getObjectMeasure werden zwar von der Qlik Code Completion vorgeschlagen, in der Dokumentation sind sie aber nur bei der Funktion "GetObjectField" angedeutet.

Die GetObject* Funktionen in der Qlik Auto Completion

Seit einigen Versionen hat auch Qlik Sense die Funktionalität Spalten (Dimensionen und Kennzahlen) in Diagrammen dynamisch ein- und auszublenden.

Hier kann man sich die getObjectDimension und getObjectMeasure zu nutzen machen, um abzufragen was denn gerade der Titel der jeweiligen Spalte ist:

Ist die Spalte "Count" eingeblendet wird "Anteil der Firmen" angezeigt

Ist die Spalte "Revenue" eingeblendet wird "Anteil des Umsatzes" angezeigt

Die Funktionen können als Kennzahl genutzt werden


oder zur Definition der Hintergrund/Textfarben. Leider kann man momentan die Funktionen nicht für die Titelbeschreibung der Charts nutzen.



Die Funktionen liefern die Beschriftung der Spalten, und nicht deren Definition.  Fügt man also zb. das Feld "Region" als Spalte ein, ändert aber den Titel auf "State" so liefert GetObjectDimension(1) auch die Bezeichnung "State".


Ändert man die Spaltenreihenfolge mittels Drag&Drop ändern sich die Formelergebnisse ebenfalls nicht. Es scheint also so, als würden die beiden Funktionen auch interne Indexe zugreifen!


Mit GetObjectDimension() kann man auch auf Alternative Dimensionen zugreifen. Zum Beispiel könnte man sagen für "Kategoriename" macht man ein normales PieChart, wenn aber auf "Artikelname" geswitcht wird, nutzt man die Radius-Formel  für ein RoseChart. Damit kann man wieder einiges steuern, wo wir in QlikView die Zyklische Gruppe & getcurrentfield()-Funktion hatten.

PieChart mit "KategorieName" als Dimension und "Artikelname" als Alternative Dimension

Wenn man auf die Alternative Dimension "Artikelname" switcht, wird ein Rose Chart daraus.

Hier die Radiusformel mit GetObjectDimension(0) um das Rose Chart zu generieren.

Die Beispiel .qvf finden Sie hier. Hier der Qlik Knowledge Base Artikel zu den Funktionen:
https://support.qlik.com/articles/000058438

Wenn man seine QlikView und Qlik Sense Section Access Berechtigungen in NPrinting wiederverwenden will, ist leider die Dokumentationslage ein wenig dünn. Im Qlik STT Video "Qlik NPrinting User Management & Security" wird das Thema ganz am Ende bei den Q&A kurz behandelt: Ja, NPrinting supported Section Access. Dieser Blogpost zeigt wie es gemacht wird!

Der "UserDACH" bekommt auch im PDF-Report nur Deutschland, Österreich, Schweiz zugesendet

Die Anleitung unterhalb zeigt wie der Section Access aus QlikView bzw. Qlik Sense in NPrinting wiederverwendet werden kann, ohne manuelles Anlegen von User Filtern  in  der NPrinting Console.



1) Section Access wird in QlikView und Qlik Sense im Script definiert. Unser Demouser ist "VPS1264656\UserDACH",  und darf drei Länder Deutschland, Österreich, Schweiz sehen .

Den NPrinting-Serviceuser (in unserem Fall VPS1264656\QVSERVICE) hier als ADMIN ohne Einschränkung hinzufügen.


 
2)  Loggt sich der "UserDACH" in die Applikation ein, sieht man wie der Section Access auf die drei Länder einschränkt. Genau die gleiche Einschränkung soll der User bei seinen NPrinting Reports haben.

Die Funktion "=osuser()" zeigt uns den eingeloggten User. Das sieht in Qlik Sense ein wenig anders aus als in Qlikview, zeigt aber prinzipiell genauso die Domain und den Usernamen.



3) Damit der User einen NPrinting Bericht erhalten kann, muss er in NPrinting angelegt bzw. aus dem ActiveDirectory importiert werden. Wichtig ist, dass das Feld "Domain Account" danach ein "Valid NT Domain User" ist, weil über diesen Usernamen der Section Access angewendet wird.


4) Beim Anlegen der Connection, muss man den Haken "Apply user section access for reports" anhaken. Als Identity tragen wir den NPrinting-Serviceuser VPS1264656\QVSERVICE ein. 


5) Den NPrinting Report wie gewünscht designen. Am besten auch eine Textbox mit osuser() auf den Bericht ziehen, damit man das Ergebnis validieren kann.

6) Die Recipients des Berichts hinzufügen. Damit mappt der NPrinting User "UserDACH" auf  den Domain User "VPS1264656\UserDACH". Das gleiche gilt für UserAT und UserDE -  und so bekommt jeder der 3 User seinen maßgeschneiderten Report
7) Der Benutzername kann dynamisch in der Email gesetzt werden


 8) Wenn Sie den Task nun anstarten, bekommt jeder User seinen "Section Access" geschneiderten Report in die Mailbox geliefert. Die osuser()-Funktion zeigt, dass Section Access greift.

Zu jedem Feld kann man sich in QlikView und Qlik Sense die Information Density anzeigen lassen. Das ist eine wichtige Information, um die Datenqualität Ihrer App zu überprüfen.

Hier ein Beispiel: im Kundenstamm haben nur 75,8% aller Kunden eine Telefax Nummer gepflegt. Das könnte bei Ihrer nächsten Telefax-Marketing Kampagne (falls es soetwas noch gibt :-)) störend, für Ihre nächste Rechnungslegung unter Umständen sogar sehr ärgerlich sein!

Information Density - links in Qlik Sense, rechts in QlikView

Jedes einzelne Feld zu überprüfen ist einigermaßen zeitaufwendig, weswegen wir in unserem Heldendaten Applikationstemplate gerne den Data Profiler von quickintelligence.co.uk nutzen. Dort sieht man die "Null Values" eines Feldes auf einem Blick.

Telefax hat 22 Null Values, was die Information Density von 75,8% ergibt

Auch andere Felder im Kundenstamm haben eine Information Density < 100 %

Information Density bei Concatinierter Faktentabelle

Schwieriger wird es mit der Information Density-Kennzahl, wenn Sie ein Datenmodell mit mehreren Faktentabellen bauen. Das passiert sehr häufig: Ihre Applikation hat mehrere Faktentypen (IST-Zahlen und Budget-Zahlen) oder Sie verheiraten die ERP-Altdaten mit Daten ihres aktuellen ERPs. Steht ist es wichtig zu wissen, welche Spalten wie gut gefüllt sind. Vor allem wenn man sicherstellen möchte, dass der Anwender mit seinen Selektionen die Daten aus allen Quellsystemen wählt (also die Information Density 100% ist)


Zur Veranschaulichung, hier ein Beispiel wie in  Qlik solche Faktentabellen aussehen:

Concat Fakten in gemeinsame Faktentabelle

Für den Faktentype Procurement Cost gibt es das Feld Region  in der Quelle nicht. Damit ist die Information Density für diese Feld  jetzt nur noch 14 (befüllte Zeilen) /20 (Zeilen insgesamt) = 70%. Viel interessanter wäre es aber zu wissen, dass das Feld Region folgende Information Densities hat:

• Sales: 100%
• Plan Yearly: 100%
• Procurement Cost: 0%

Wählt ein Anwender einen Eintrag im Feld Region an der Oberfläche, so sieht er keine Procurment Costs mehr. Das kann gewünscht sein, oder ein fehlerhaftes Mapping/fehlende Daten sein. Eine Information Density pro Faktentyp ist also Notwendig für eine umfassende Problemanalyse.

Dafür benötigen wir zuallererst ein Feld Source um zu wissen aus welcher Quelle welche Datensatzzeile ursprünglich kommt. Das Aufbauen Ihrer Faktentabelle Facts sollte also etwa so aussehen (je nachdem ob aus Datenbank/QVD/resident)

Facts:
load
*,
'Sales' as Soure
from/resident Sales;

concatenate (Facts)
load
*,
'Plan Yearly' as Soure
from/resident Plan Yearly;

concatenate (Facts)
load
*,
'Procurement Cost' as Soure
from/resident [Procurement Cost];

Als nächsten Schritt müssen NullCounts() für alle Felder im Script berechnet werden. Wenn Sie ein 3-Schichtenmodell nutzen, würde ich das Script im Datenmodell hinzufügen. Bei großen Datenmengen und vielen Faktenspalten kann das Skript schon mal länger rechnen - fügen Sie sich vielleicht einen Variablen-Schalter ein, damit das Skript beim Entwickeln nicht ständig ausgeführt wird.

//config
let tFactTableName = 'Facts';
let tSourceTable_Field = 'Source';


//Generate Fields that are in Facttable
let vAggregations = '';
for i = 1 to nooffields('$(tFactTableName)')

 let vField = Fieldname($(i),'$(tFactTableName)');
 if (vField <> '$(tSourceTable_Field)') then
   let vAggregations = vAggregations & 'nullcount([$(vField)])  as [$(vField)] ,';
 end if
 
 
 
next
trace *************** Check for fields: $(vAggregations);

ttFactsInfo:
load
   $(tSourceTable_Field), 
   $(vAggregations)
   1 as dummy
resident $(tFactTableName) group by $(tSourceTable_Field);

drop field dummy;


//Transform table to get all Fieldnames in a single column
tFactsInfo:
CrossTable (FieldName,NullCount,1)
load
  *
resident ttFactsInfo;
drop table ttFactsInfo;
 
  
//Check how many rows each "Source Table" has
MappingFactsSourceCount:
mapping
load
 $(tSourceTable_Field),
 count($(tSourceTable_Field))  as AllCount
resident $(tFactTableName) group by $(tSourceTable_Field);


//Generate FactsInfo
FactsInfo:
load
 $(tSourceTable_Field),
 FieldName,
 applymap('MappingFactsSourceCount',$(tSourceTable_Field), -99) 
           as AllCount,
 NullCount,
 applymap('MappingFactsSourceCount',$(tSourceTable_Field), -99)-NullCount 
          as FoundCount
resident tFactsInfo;

drop table tFactsInfo;

//optional drop table Facts;


let tFactTableName = ;
let tSourceTable_Field = ;

Das Skript erzeugt einen neue Tabelle FactsInfo mit den Feldern NullCount und FoundCount für jeden Fieldname pro Source-Tabelle. Damit lässt sich dann eine Pivottabelle bauen, die Ihnen die Information Density pro Feld zeigt. Unterhalb sehen Sie ein beladenes Beispiel in Qlik Sense

Information Density für die Fakten: Buget, ORDERS und VBRP

Die Gesamtwerte-Spalte zeigt die Information Density wie sie Qlik im Datenmodell anzeigen würde. Daneben sehen wir die Befüllung in den Faktenquellen. Man sieht zum Beispiel auf einen Blick, dass das Feld PRODH (Produkthierarchie) in einigen ORDERS-Faktenzeilen nicht befüllt ist, da die Information Density dort nur 99,98% ist. Das Feld %KUNNR hat leere Einträge in ORDES und VBRP. Die Spalte NetSalesBudget hat nur eine Information Density von 1,94% - das ist aber OK weil es nur in der Source-Tabelle Budget befüllt ist (und dort dafür mit 100% immer einen Wert hat)

Als keines Gimmick sei auch noch das Sortieren der Pivot-Tabelle genannt. Sowohl die Sortierung nach Information Density, als auch eine alphabetische Sortierung der Feldnamen kann Sinn machen. Mit einer Variable kann die Sortierung hin- und hergeschalten werden. Siehe Screenshot mit alphabetisch sortierten Feldnamen - jetzt mit QlikView:

Information Density alphabetisch sortiert

Das komplette Beispiel für QlikView und Qlik Sense finden Sie unter content.heldendaten.eu/InformationDensity.zip

Bei Connectivity gilt für viele Leute das alte Sprichwort "Never touch a running system". Das gilt auch sehr stark für den Qlik SAP Connector. Einmal installiert, liefert er zuverlässig die Daten an die Qlik Platform - über ein Upgrade denken da die wenigsten nach.

Ab und zu einen Blick auf die aktuellen Releasenotes zu werfen, schadet aber nicht! Zuletzt kam mit Version 5.9 der neue BEx Connector hinzu, und auch sonst beinhaltet der aktuelle Connector einige Neuerungen. Unterhalb sehen Sie die aktuellen Zugriffsmöglichkeiten auf Ihr SAP System.

Zugriffe auf SAP ERP

Zugriffe auf SAP BI/BW

Seit Frühjahr 2016 liegt der  "Qlik Connector for use with SAP 6.1.2" vor. Die ReleaseNotes beinhaltet eine verlockende Aussage, gerade für Kunden die noch die Versionen 5.80 oder 5.90 im Einsatz haben:

• SQL Connector: Significant performance improvement for reload times

Das Upgrade will aber gut geplant sein, beinhaltet es doch einige Fallstricke!

Änderung des Connection Strings

Mit dem Support von Qlik Sense, hat sich auch der Connection String des SAP Connector ab Version 6.1 geändert. Wenn Sie den Connection String ordentlich in einem Include File pflegen, sollte das Umstellen mit einem geringen Aufwand verbunden sein. Ändern Sie einmalig .dll in .exe und beim OpenSQL Connector müssen Sie "Sql" einfügen!

Old connection string for QlikView:
CUSTOM CONNECT TO "Provider=QvSAPConnector.dll;…

New connection string for QlikView:
CUSTOM CONNECT TO "Provider=QvSAPSqlConnector.exe;…

Falls Sie den Connectionstring in mehreren Applikationen stehen haben - spätestens jetzt wäre der Zeitpunkt für ein Include-File!

Aktuelle Transporte

Mit jeder Version des SAP Connectors werden auch SAP-seitige Qlik Module ausgeliefert. Diese Transporte sollten eigentlich immer gleichgezogen mit der aktuellen Connector-Version auf Ihrem Windows-Server werden. Seit dem Release 5.80 SR2 gab es da eine gewisse Abwärtskompatibilität, weswegen das gerne vernachlässigt wurde. Der Einzige der sich beschwert hätte, ist der Qlik SAP Log! Den finden Sie unter C:\ProgramData\QlikTech\CustomData\QvSAPConnector\Log


Mit der Version 6.1.2 sind die Transporte auf jeden Fall wieder hochzuziehen, wenn Sie den SQL Connector oder den BEx Connector im Einsatz haben. Siehe die Tabelle aus den Releasenotes, die neue Kompatibilität für SQL+BEx Connector beginnt mit 6.1.1.




Einen Überblick welcher Transport in welcher SAP Basis Version zu welcher Connector-Version passt, finden Sie im QlikSupport hier.

Änderung der Datenanlieferung - Führende Nullen

Die größte Änderung verbirgt sich in einem unscheinbaren Satz in den Release Notes zu Version 6.1 IR.

The SAP data types CHAR (character) and NUMC (numeric character) were previously interpreted (in the Qlik script) as ‘numeric’ if they contained only digits. Leading zeroes were removed. (0000141000 became 141000).

Diese Änderung hat aber gravierende Auswirkungen auf bestehende Skripte. Wir haben Fälle gesehen, wo als Folgewirkung die Datenmodell keine Daten mehr aus den .qvds geladen haben. Zum Beispiel lieferte folgender Pseudocode

from .qvd
where MANDANT = '10' 

plötzlich nur noch 0 Zeilen!

Alle Skripte mussten auf

from .qvd
where MANDANT = '010' 

geändert werden.

Manche Schlüssel und Datenfelder änderten ihren Typ von Zahl auf Text, was in den Layouts zu "unbrauchbaren" Applikationen führte. Wir konnten nur mittels Informationen im QVD Header rausfinden, welche Felder alle betroffen sind.

Das Verhalten ist ebenfalls problematisch, wenn Sie bestehende .qvds haben und nur einen Delta-Load durchführen. Alte .qvds, erzeugt mit einer Version vor 6.1 IR, sind sozusagen nicht kompatibel. Sie müssten einen Fullload machen. Also alles Punkte die mit sehr viel Aufwand verbunden sind.

Nach Rückfragen beim Qlik Support gibt es mit 6.1.2 nun jedoch ein undokumentiertes (aber supportetes!) Connectionstring-Setting DataTypeLegacyMode=1. Wir hatten noch nicht die Möglichkeit es zu testen, aber ich hoffe damit bleiben uns viele Skriptumstellungen erspart.

Zusammenfassung

Egal von welcher Version des Connectors Sie updaten, ein Upgrade will geplant sein! Bitte zuerst die Transporte am SAP Testserver updaten, und dann das Qlik Setup auf dem Windows-Server durchführen. Wenn dann alle Qlik Skripte sauber durchlaufen, können Sie das Update der Produktion in Angriff nehmen!

Nachtrag 24.03.2017

Folgenden Knowledgebase Artikel gibt es nun von der Qlik bezüglich Datumsfeldern zu beachten:

Vor Kurzem hat uns ein Kunde eine "eigenwillig" formatierte Datei zukommen lassen, mit der Bitte diese in eine Qlik Analyse einzubauen. Jedes BI-Tool kann wohl Excel, .csv oder XML-Daten laden. Aber so simpel war diese Datei leider nicht gestrickt. Dass wir für solche Fällen die Qlik ETL Skriptsprache zur Verfügung zu haben, ist natürlich äußerst dienlich!

Die Datei hatte folgende Struktur

CreationTime   : 16.03.2015 10:21:39
LastWriteTime  : 15.11.2014 14:28:45
LastAccessTime : 16.03.2015 10:21:39
Length         : 3512312
FullName       : \\MyComputer\Pics\P101.JPG

CreationTime   : 16.03.2015 10:21:39
LastWriteTime  : 15.11.2014 14:28:45
LastAccessTime : 16.03.2015 10:21:39
Length         : 2794726
FullName       : \\MyComputer\Pics\P104.JPG

CreationTime   : 16.03.2015 10:21:40
LastWriteTime  : 15.11.2014 14:35:02
LastAccessTime : 16.03.2015 10:21:40
Length         : 3151733
FullName       : \\MyComputer\Pics\P107.JPG

CreationTime   : 16.03.2015 10:21:41
LastWriteTime  : 15.11.2014 14:35:02
LastAccessTime : 16.03.2015 10:21:41
Length         : 3451760
FullName       : \\MyComputer\Pics\P108.JPG.JPG

und sollte in diese Struktur gebracht werden:

Um die Daten in diese tabellarische Form zu bekommen, sind einige Transformationsschritte notwendig.

Feldspalten trennen

Die Tabelle  lässt sich nicht gut anhand eines Trennzeichen teilen, weil das vermeintliche Trennzeichen "Doppelpunkt" zwischen den Wert-Paaren genauso vorkommt wie als Trennzeichen von Stunden, Minuten und Sekunden.

CreationTime   : 16.03.2015 10:21:41

In Qlik kann man stattdessen beim Einlesen der Datei auf die Option "Fix-Record" setzen. Nach 17 Zeichen hätten wir gern eine neue Spalte um Attribut-Felder von Wert-Feldern zu trennen.



Qlik generiert daraus folgendes Skript für die Spalten "Feld" und "Wert"

LOAD [@1:16] as Feld, 
     [@17:n] as Wert
FROM
[.\data.txt]
(fix, codepage is 1252);

Daten bereinigen

Für die weitere Transformation  benutzen wir einen Load above Load Befehl. Die Leerzeile die die  Originaldatei nach je 5 Zeilen enthält, laden wir danke len(Feld) > 0 nicht mehr mit. Den Doppelpunkt in der Spalte "Feld" entfernen wir mittels String-Befehl purgechar. Die leading und trailing Spaces, löschen wir mit trim().

DataTemp:
Load
ceil(rowno()/5) as ID,
trim(PurgeChar(Feld,':')) as Feld,
trim(Wert) as Wert
where len (Feld) > 0;
LOAD [@1:16] as Feld, 
     [@17:n] as Wert
FROM
[.\data.txt]
(fix, codepage is 1252);

Nach den bisherigen Transformationsschritten sind Attribute und Werte fein säuberlich getrennt. Sie stehen jedoch weiterhin in mehreren Zeilen untereinander.

Zeilen in Spalten transponieren

Um die fünf zusammengehörigen Attribute nun in eine gemeinsame Zeile mit mehreren Spalte zu transponieren, würde mancher QlikView-Kenner an den Generic-Load denken. Wir machen es hier aber selbst. Der Trick verbirgt sich hinter dem Feld (ceil(rowno)/5) as ID welches wir im vorherigen Skriptabschnitt eingebaut haben. Durch die Division&Aufrunden wissen wir immer welche fünf Zeilen in eine gemeinsame Zeile zusammengejoint werden müssen.

Die For-Schleife übernimmt dann den Join, und liefert uns das finale Ergebnis.



Voilà! Transformation geschafft! Auch wenn die meisten Daten nicht so "gemein" formatiert sind, ist es immer gut zu wissen eine mächtige Skriptsprache zur Verfügung zu haben! Also: Viel Spaß beim Qlik Skript schreiben!

Im Dezember 2014 habe ich im Artikel Happy Holiday mit QlikView - Feiertage aus dem Internet holen beschrieben, wie man mit sich mit dem Qlik Skript die Feiertage aus dem Internet holen kann. In der Zwischenzeit hat die Webseite http://www.feiertage-oesterreich.at/ die Feiertage bis 2025 ergänzt, aber leider auch gleich ihr Layout abgeändert. Zeit das Skript anzupassen!

Im Kern ist das Skript gleich geblieben, nur die HTML-Tabelle hat sich etwas geändert. Die Schleife läuft dafür bis ins Jahr 2025.

let vTable = 'Feiertage:';
for i = 2013 to 2025
       $(vTable)
       load
        BeschreibungFeiertag,
        Feiertag_Tag,
        Feiertag_Monat,
        $(i) as Feiertag_Jahr,
        makedate($(i),Feiertag_Monat,Feiertag_Tag) as DatumFeiertage,
        1 as flagFeiertag;
       load
        BeschreibungFeiertag,
        left(Datum,2) as Feiertag_Tag,
        mid(Datum,4,2) as Feiertag_Monat;
        
        LOAD right(@1,6) as Datum,
        @2 as BeschreibungFeiertag
       FROM
       [http://www.feiertage-oesterreich.at/$(i)]
       (html, utf8, no labels, table is @3) where trim(@3)='§';
       
       let vTable = 'concatenate(Feiertage)';

next

Mit ein wenig Firlefanz rundherhum sieht der Kalender dann so aus!

Feiertage 2016 in QlikView ...

... und Feiertage 2017 in Qlik Sense

Die gesamte Applikation für QlikView und Qlik Sense sowie als .csv und .qvd (falls sich das Webseiten Layout wieder ändert) finden Sie als Download hier.

PS: Achtung: 24. und 31. Dezember sind keine gesetzlichen Feiertage. Aber da sie jedes Jahr am gleichen Tag sind, lasse ich das als kleine Hausübung wenn Sie das Skript einsetzen möchten :-)

Mit QlikView kann man seit der Version 10 Kommentare für Feldnamen hinterlegen, um etwa zu erklären aus welcher Datenquelle das Feld stammt und was das Feld beinhaltet (Klassische Frage: enthält das Feld Datum das Bestelldatum, das Rechnungsdatum oder das Lieferdatum?). Dieses Feature kam bisher vor allem Entwicklern und Fachanwendern zu gute, die selbst QlikView Applikationen entwickeln. Seit QlikView 11.20 Service Release 9 werden diese Feldbeschreibungen über den QlikView Accesspoint nun allen Benutzern bereitgestellt.

Beschreibende Infos liegen uns am Herzen. Wie wir hier beschrieben haben, ist es unserer Meinung ideal bereits in der Entwicklung am QVD-Layer eine Beschreibung für jedes Feld zu hinterlegen. Hat man beispielsweise SAP kann man aus dem Data Dictionary die Feldbeschreibung automatisch nach QlikView übernehmen.

An der Oberfläche hatte man bisher schon die Möglichkeit bei jedem Objekt ein Kommentar zu hinterlegen. Ist unter "Eigenschaften|Titelleiste" ein Kommentar eingetragen, so erscheint beim Objekt ein Fragezeichen-Icon. Wer mehr als einen kurzen Text erklären möchte, kann mittels unserer Document Extension auf ein Wiki/eine Webseite abspringen.
 

Für Applikationserklärung hat sich über die Jahre der "Info"-Button durchgesetzt. Mittels Show/Hide-Bedingungen werden Objekte eingeblendet, die die Funktionalität bzw. Inhalt der Applikation erklären.


In QlikView 11.20 SR9 ist uns eine Neuerung im IE-Plugin aufgefallen: Fährt man in der Auswahlbox über ein selektiertes Feld, so wird das im Skript hinterlegte Feld-Kommentar als Tooltip angezeigt.

Das ist sehr praktisch, denn im Gegensatz zum Ansatz mit "Titelleiste|Kommentar", hat man für Gewöhnlich nicht für jede Feldselektion eine eigene Listbox bei der man ein Kommentar hinterlegen kann (weil man zB. Multibox oder Globale Suche nutzt). Weiters hat der Benutzer in der Auswahlbox nun einen zentralen Punkt wo er alle Felderklärungen sehen kann, sobald er einen Filter darauf gesetzt hat.

Der "COMMENT Fields" Befehl im Skript sorgt für die Erklärung des Feldes "Kunden.Firma" in der Auswahlbox

• Achtung wenn Sie bereits Feldbeschreibungen definiert haben, die nicht für jedermann bestimmt sind! Nun sehen auch all Ihre Anwender das Kommentar
• Fügen Sie das COMMENT Field erst nach dem BINARY-Load ein; sonst gehen die Kommentare verloren
• Im AJAX-Client sehen wir das Kommentar als Tooltip noch nicht. Wir werden uns schlau machen, ob wir dieses sinnvolle Feature auch dort bekommen können.

Der LOAD-Befehl kennt viele Quellen aus denen er sich befüttern lässt: OLEBC/ODBC-Datenquellen, .csv-Dateien, SAP, QVSource. Gerade eben habe ich mir den QVX-Connector von Rapidminer angesehen - nur um eine kleine Auswahl anzuführen.

Einen  weiteren LOAD-Befehl gibt es zwar laut Dokumentation, er ist aber in der freien Wildbahn  höchst selten anzutreffen:

LOAD FROM FIELD

Die Idee: ich habe ein Feld bereits geladen, möchte aber dessen Inhalt nochmals als Datenstruktur weiterverarbeiten. Wieso möchte ich das? Nach einigen Jahren ist mir das erste Mal ein sinnvolles Beispiel untergekommen: Ein Vorsystem liefert uns eine .csv-Datei,  in der das Feld "Data" weitere Informationen als XML-Struktur enthält.



Ladet man das Feld "Data" ohne weitere Transformation, ist es nur bedingt nützlich.

Das Feld "Data" enthält nur einen langen XML-String

Ziel ist es, die einzelnen XML-Tags und Attribute wie  PNR, Name und ID aus dem Feld "Data" zur Analyse bereitzustellen. Genau hier kommt FROM FIELD zu Tragen.

Table:
LOAD 
  rowno() as rowid,
  Department,
  Data
FROM
[.\text.csv]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);



//Loop and Table "Helper" with just a single row is necessary, 
//otherwise the "From Field" fails in QV11.20SR10. 
//Bug accepted by Qlik Support.
for i = 1 to (NoOfRows('Table'))

 Helper:
 load
   Data as XmlField
 resident Table where rowid=$(i);
 


 //Trick: rowno() so we can join with the original Table 
        //to retrieve "Department" field
 XMLResult:
 load
   $(i) as rowid,
   [PNR], //access xml Tag
   [Name],
   [Id] //access xml Attribute
 From_Field (Helper,XmlField)
 (xmlSimple, table is [Info/Data]);
 
 drop table Helper;

next

Das Listing ist nicht ganz trivial, aber das Ergebnis erfolgreich: Aus meinen drei Inputzeilen werden vier Mitarbeiter generiert. Der Verweis zurück zu meinem "Department" funktioniert über den Trick mit rowno(). Im Screenshot unterhalb sieht man, dass die Person "RVA" dem "Sales"-Department zugeordnet ist.



Unterhalb einige Notizen zum Skript:

• Zeile 1: Die .csv-Datei ganz normal laden. In diesem Fall hat sie eine '|' als Trennzeichen
• Zeile 15: Der FROM FIELD -Load  funktioniert momentan nur wenn man einzelne Zeilen liest. Deswegen müssen wir hier in einer Schleife über die Tabelle iterieren. Das ist nicht furchtbar schnell, aber momentan als Workaround notwendig. Das Problem ist beim Qlik Support eingekippt. Ich melde mich, sobald es in einem Service Release gefixt wurde.
• Zeile 32: Hier kommt das FROM FIELD zum Einsatz. Das bereits geladene Datenfeld Data wird hier nochmals als XML geladen --> hier werden die XML-Infos. ID, NAME,PNR aus dem XML geholt.

Eine QlikView .qvw, eine Qlik Sense.qvf und die Inputdatei zum Ausprobieren finden Sie hier.

Wer sich über die schlechten Umsätze in der ersten Kalenderwoche 2015 wundert, kann das den günstig gefallenen Fenstertagen (oder wie man in Deutschland sagt: "Brückentage") zuschreiben. Man sollte aber auch einen Blick auf sein QlikView-Skript werfen!

Hat man in seinem Masterkalender die unscheinbare Skriptzeile

    Week(Datumsfeld)&'-'&Year(Datumsfeld) AS [Eindeutige Woche]

definiert, dann hat die erste Kalenderwoche 2015 nur 4 Tage. Der Grund: Die KW 1 2015 geht von 29.12.2014 bis 04.01.2015. Drei Tage des Jahres 2014 gehören also in die erste Kalenderwoche von 2015. Definiert ist diese ganze Kalenderwochenlogik in der ISO8601, und - wie man hier nachlesen kann - prinzipiell verhält sich QlikView auch nach diesem Standard.

Das Problem

Anbei ein anschauliches Beispiel: wir arbeiten für ein Unternehmen, dass an jedem Kalendertag genau 100 EUR Umsatz macht.  Entsprechend würden wir auch erwarten, dass in jeder Kalenderwoche genau 700 EUR Umsatz aufsummiert werden können.

Unser Datentopf: jeden Tag 100 EUR Umsatz macht eine hübsche Gerade

Benutzen wir die oben erwähnte Formel für die Kalenderwoche, sehen wir aber, dass in den letzten Jahren einige Wochen mit 800  EUR (also 8 Tagen), und dieses Jahr die KW 1 mit nur 400 EUR (also nur 4 Tagen) existiert.


Der Grund wird klar, wenn man sich die Datumswerte zum Jahreswechsel ansieht. Die Tage 29. bis 31.12.2014 werden von der week()-Funktion nach ISO8601 in die KW1 gesetzt, aber da die Year()-Funktion das Kalenderjahr nimmt, landen diese 3 Datensätze fälschlicherweise in der Kalenderwoche "1-2014".

1-2014 ist die falsche KW für diese 3 Tage

 

Die Weekyear() - Funktion

Um das Problem zu lösen, bietet sich die Funktion weekyear() an, die genau diese Ungereimtheiten löst und für die drei Tage das Jahr der Kalenderwoche liefert.

Week(Datumsfeld)&'-'&WeekYear(Datumsfeld) AS [Eindeutige Woche]

Weekyear() schiebt die 3 Tage ins Jahr 2015

Entsprechend sieht das Chart dann auch wie ursprünglich erwartet aus: jede Woche hat 700 EUR Umsatz, weil jede Woche aus 7 Tagen besteht.


Wer bisher schon die Weekname ()- Funktion benutzt hat, war auf der sicheren Seite. Nur das Format "YYYY/WW" ist nicht jedermanns Sache. Um also ein frei formatierbares Wochenfeld zu haben, dass sich auch gut numerisch sortieren lässt, schlagen wir folgende Felddefinition vor:

dual(
     Week(Datumsfeld)&'-'&WeekYear(Datumsfeld),
     num(weekname(Datumsfeld))
)
AS [Eindeutige Woche]

Das komplette Beispiel findet sich hier!