The Lost Datarows: DISTINCT - Verhalten bei Join, Concatenate

Normalerweise erzähle ich meinen QlikView-Novizen immer, dass man ein QlikView Skript von oben nach unten (und - falls Reiter vorhanden - von links nach rechts) liest. Innerhalb eines Load&Select-Statements gilt die Ausnahme, dass man diesen Statement-Block von unten nach oben lesen muss. Zuerst kommen die Daten aus einer Datenquelle (unten), dann werden sie mit einem (oder mehreren) Load-Statement weiterverarbeitet.

Hier ein einfaches Beispiel - aus der Ursprungstabelle lade ich mit einem oberhalb liegenden LOAD DISTINCT die eindeutigen Preise für die 3 Produkte. Das Statement muss von unten (Inputtabelle hat 4 Zeilen*3 Spalten) nach oben  (Ergebnistabelle hat 3 Zeilen*2 Spalten) gelesen werden.

Es gibt jedoch einen Fall den man genauer betrachten muss: LOAD DISTINCT in Kombination mit JOIN bzw. CONCATENATE:

Ergänzt man das Skript um einen Left Join auf die Faktentabelle, sieht das Skript folgendermaßen aus:

Facts:
LOAD * INLINE [
    Year, Product, Quantity
    2013, ProductA, 500
    2013, ProductA, 500 
    2013, ProductB, 300
    2012, ProductA, 700
    2012, ProductB, 400
];

left join (Facts)
Load
Distinct
 Product,
 Price;
LOAD * INLINE [
    Product, Price, Variante
    ProductA, 100, X
    ProductA, 100, Y
    ProductB, 10, A
    ProductC, 700, A
];


Liest man das Skript nach den genannten Regeln, würde man folgendes Join-Verhalten erwarten:
  • (Schritt -1: Die Faktentabelle wird  mit 5 Zeilen geladen)
  • Schritt 0: Die Produkttabelle wird mit 4 Zeilen INLINE geladen
  • Schritt 1: Load Distinct reduziert die Produkttabelle auf 3 Zeilen
  • Schritt 2: Der Left Join beläst per Definition die Faktentabelle (linke Tabelle) gleich und ergänzt die passenden Preise aus der Produkttabelle (rechten Tabelle).
Symbolische Darstellung des "zu erwartenden" Verhalten

Als Ergebnis würde man sich also eine Quantity von (500+500+300+700+400) = 2400 erwarten.

Lässt man das Script aber in QlikView laufen, erhält man überraschenderweise ein Chart mit der Quantity 1900:


Quanity für ProductA ist 2013 nur 500
Was ist passiert? Die Quantity für ProductA ist im Jahr 2013 nur 500, statt der ursprünglichen 1000 aus der Faktentabelle. Die Faktentabelle enthält auch nur 4 statt der ursprünglichen 5 Zeilen. Es scheint also, als ob der LEFT JOIN Einfluss auf unsere Faktentabelle genommen hat.

Betrachtet man das Ergebnis genauer, so ist es nicht der LEFT JOIN, sondern vielmehr das Keyword DISTINCT auf der Produkttabelle das Schuld an unserem fehlenden Datensatz ist. Das DISTINCT hat nicht nur Einfluss auf die geladene Produkttabelle, sondern reduziert auch alle Einträge in der Faktenabelle auf eindeutige Werte.  


Beim Lesen eines QlikView Skrips muss also folgende zusätzliche Regel beachtet werden:


DISTINCT wird in QlikView nicht auf die Inputtabelle angewandt, sondern am auf die gesamte, geladene Tabelle! Wird eine Tabelle also einmalig als DISTINCT markiert (direkt oder indirekt [durch Join/Concatenate]) sind die Zeilen diese Tabelle in allen weiteren Operationen IMMER DISTINCT!

Um aus der ursprünglichen Faktentabelle nicht "versehentlich" Zeilen zu entfernen, benötigen wir somit eine Zwischentabelle. Das Skript sieht dann folgendermaßen aus

FixFacts:
LOAD * INLINE [
    FixYear, FixProduct, FixQuantity
    2013, ProductA, 500
    2013, ProductA, 500 
    2013, ProductB, 300
    2012, ProductA, 700
    2012, ProductB, 400
];

Temp:
Load
Distinct
 FixProduct,
 FixPrice;
LOAD * INLINE [
    FixProduct, FixPrice, FixVariante
    ProductA, 100, X
    ProductA, 100, Y
    ProductB, 10, A
    ProductC, 700, A
];

left join (FixFacts)
load
*
resident Temp;

drop table Temp;
 
 
Quantity 2400 als korrektes Ergebnis

Hier klappt es nun wie erwartet, weil nur die "Temp"-Tabelle mit dem Keyword DISTINCT markiert wurde. Die "FixFacts" Tabelle ist an keiner Stelle mit dem Keyword DISTINCT versehen. Somit bleibt das ProduktA im Jahr 2013 mit einer Quantity von 500 zweimal in der Tabelle erhalten.

Ein zweites Szenario wo man dieses Verhalten manchmal unabsichtlich auslöst, ist der Befehl CONCATENATE. Das Skript unterhalb löscht Zeilen aus meiner Istdaten-Tabelle "ConcatFacts", obwohl ich eigentlich nur die Plandaten DISTINCT daran "anhängen" wollte:

ConcatFacts:
LOAD * INLINE [
    CYear, CProduct, CQuantity
    2013, ProductA, 500
    2013, ProductA, 500 
    2013, ProductB, 300
];

//Append Plan Data
concatenate(ConcatFacts)
LOAD distinct * INLINE [
    CYear, CProduct, Plan
    2013,ProductA, 900
    2013,ProductA, 900
    2013,ProductB, 200
]; 
 
Ist-Quantity von ProduktA ist nach CONCATENATE & DISTINCT nur 500

Auch hier hilft wieder der Umweg über eine temporäre "DISTINCT"-Tabelle
FixConcatFacts:
LOAD * INLINE [
    CCYear, CCProduct, CCQuantity
    2013, ProductA, 500
    2013, ProductA, 500 
    2013, ProductB, 300
];

//Append Plan Data
Tmp:
LOAD distinct * INLINE [
    CCYear, CCProduct, CCPlan
    2013,ProductA, 900
    2013,ProductA, 900
    2013,ProductB, 200
];

Concatenate(FixConcatFacts)
load
*
Resident Tmp;

drop table Tmp;


Quantity 1000 für ProductA als korrektes Ergebnis


Zuletzt hatten wir dieses "Phänomen" am MasterSummit for QlikView diskutiert. Das Verhalten ist zumindest seit QlikView 8.* (meiner ersten QlikView Version) konsistent. Falls Sie diese Eigenschaft von DISTINCT noch nicht kannten, und Ihnen in Ihren Skripten Datenzeilen abgehen, durchsuchen Sie Ihre QlikView Applikationen nach den beschriebenen Mustern! Das komplette Beispiel zu diesem Blogeintrag findet sich unter http://content.heldendaten.eu/DistinctVerhalten.zip




Auf in eine transparente Zukunft

Vor einigen Wochen hatte ich die Anfrage alle "zukünftigen" Balken in einem gestapelten Balkendiagramm transparent darzustellen. Konkret ging es um Lagerbewegungen die bis in die Zukunft projiziert werden. Die Transparenz soll dem Anwender helfen zu sehen, ab welchem Monat es sich um "Forecast"-Zahlen handelt.

Aufgrund der Vielzahl an Produkten war es nicht gewünscht bereits im Skript Farben für jedes Produkt zu hinterlegen. Deswegen wollten wir die Standardfarben von QlikView benutzen.

Die Aufgabenstellung: Ab November (aktuelles Monat) die Balken transparent darstellen)

Mein erster Ansatz funktionierte, verlangt aber viele unschöne Stringoperationen:

if(Month >= month(today()),
 argb(120,
   mid(color(rowno()),5,3),
   mid(color(rowno()),9,3),
   mid(color(rowno()),13,3)
    )
 ,
 color(rowno())
)

Aus der aktuellen Farbe des Balkens color(rowno()) schneidet die Formel die Stellen für RGB aus, und füge Sie mit Transparenz 120 wieder in die ARGB()-Formel ein.

Farben in QlikView

Für eine bessere Lösung muss man sich zuerst klar werden, wie Farben in QlikView funktionieren. Jede Farbfunktion in QlikView liefert eine Zahl zurück. Ruft man etwa =num(black()) auf, bekommt man folgenden dezimalen Output:

Was auf den ersten Blick nach einer zufälligen Dezimalzahl aussieht, ist bei näherer Betrachtung eine Bit-Darstellung einer ARBG-Farbe. QlikView bietet fertige Funktionen um Zahlen in beliebigen Zahlenbasen darzustellen. Mit der Funktion =num(black(),'(hex)') bekommt man den aussagekräftigeren Hexadezimalen-Wert für die Farbe Schwarz: ff000000!

QlikView liefert also für jede Farbe einen ARGB-Wert zurück. Insgesamt haben wir als für jede Farbinformation 1 Byte = 8 Bit zur Verfügung. black() ist also nichts anderes als Rot=0, Grün=0, Blau=0 mit einer vollen Deckkraft von A=FF(hex)


Die Farbe Schwarz binär dargestellt liefert QlikView mit der Funktion =num(black(),'(bin)').
Man erinnere sich an seine Informatik-Ausbildung: 1Hex-Stelle sind 4 bit!

 

Transparent rechnen

Mit diesem Wissen lässt sich nun die Transparenz viel einfacher manipulieren.  Um den Transparentwert beispielsweise zu "dreivierteln" könnte man das 30. bit auf 0 setzen. Um das zu erreichen, kann man von der Farbe  2 hoch 30 abziehen: =num(black() - pow(2,30),'(bin)')

Zurückkommend auf die ursprüngliche Fragestellung, kann man jetzt komplett ohne String-Operationen auskommen. Die Formel um die Balken halb-transparent darzustellen lautet nun

if(Month >= month(today()),
 color(rowno()) - pow(2,31)
 ,
 color(rowno())
)

Das fertige Chart

Download Beispiel

Qv.exe /nodata als Shortcut am Desktop

Arbeitet man mit großen QlikView Applikationen, kann das Öffnen einer .qvw von der Festplatte schon mal etwas dauern. Will man aber nur schnell das Skript nachlesen, oder eine komplizierte Formel (bzw. ein ganzes Objekt) kopieren, ist es oft gar nicht notwendig die .qvw mit gesamten Daten zu öffnen.

Hat man die QlikView Applikation unter den "Zuletzt geöffneten Dateien" auf der Startseite gelistet, kann man mittels Rechtsklick|'Applikation ohne Daten öffnen' wählen.
Hat man die .qvw jedoch nicht in der Startliste, so müßte man kompliziert die Qv.exe mit dem Commandlineparameter /nodata benutzen. Wem das zu kompliziert ist, kann sich einen entsprechenden Shortcut am Desktop anlegen. Einfach den Pfad

                                        "C:\Program Files\QlikView\Qv.exe" /nodata


als Shortcut anlegen. Wenn man nun mittels Drag&Drop die .qvw auf den Shortcut fallen läßt, dann öffnet der QlikView Developer die Applikation ohne Daten. Die Applikation zeigt nun alle Listboxen mit "nicht verfügbar" an, aber das Skript und die Objektdefinitionen sind schnell griffbereit.

Eine kleine Warnung: Speichern Sie bitte die .qvw nur mit großer Vorsicht, nachdem Sie diese mit /nodata geöffnet haben. Leider werden bei diesem Feature nämlich keine Frontend-Variablen mitgeladen. Speichern Sie also die .qvw, dann gehen diese Variablen verloren. Gleiche Problematik hat übrigens auf das "-prj"-Ordner Feature für SVN/Team Foundation Server Integration! Als Workaround hilft nur, alle Variablen bereits im Skript zu definieren!

Update QV11.20SR4 12.11.2013
Öffnet man eine QlikView Applikation mit /nodata in  QV11.20 SR4 12129 werden Variablen nun korrekt mitgeladen. In den Release-Notes konnte ich zwar keine Anmerkung finden, aber wie der Screenshot unterhalb zeigt, sind bei meinem Test alle 3 Frontend-Variablen befüllt (nur das Tabellendiagramm kann natürlich nicht dargestellt werden, weil ja keine Daten geladen wurden). Zum Vergleich: QV10SR4 zeigt leere Variablendefinitionen.



Für den -prj-Folder konnte ich keine Änderung feststellen, also hier ist die Warnung weiterhin aufrecht!









QlikView Master Summit - Drop Fields Diskussion

Rob Wunderlich hat sich in seinem aktuellen Blogpost einer unserer Diskussionen vom MasterSummit for QlikView in Barcelona angenommen.

Falls Sie Rob's Document Analyzer benutzen, um Felder am Ende des Skripts zu droppen, hier nochmals meine Beobachtung.

Attached a small example that shows the issue:

- 01_testDatabase.qvw produces a .qvw with about 350MB on disk
- 02_testDatabaseBinaryDrop400fields.qvw does a BINARY Load and then drops 400 fields --> it's about 18 MB on disk (136 MB in Memory including QV.exe)

So one would expect that 18 MB is the final size of the .qvw!
However when I do an additional RESIDENT-Load after dropping fields, the .qvw gets much smaller:

- 03_testDatabaseBinaryDrop400fields_Resident does a BINARY Load, then drops 400 fields and THEN makes a RESIDENT LOAD on the table, dropping the original table

--> suddenly the .qvvw is only 1.2 MB on disk (26 MB in Memory including qv.exe)!
Download Example

Rob hat dies nun weiter analysiert und bemerkt, dass die  Record pointers nicht freigegeben werden.  Erst mit einem weiteren RESIDENT-Load wird tatsächlich der komplette DISK/MEMORY-Space freigegeben.

  • Im Moment ist es besser die nicht benutzen Felder im Skript auszukommentieren, anstatt Sie erst am Ende des Skripts zu droppen!
  • Falls dies nicht möglich ist, sollte man nach den Drop Fields Statement die (Fakten)-Tabelle nochmals resident laden.


Masters Summit for QlikView

Vergangene Woche hatte ich die Gelegenheit drei Tage am "Masters Summit for QlikView" in Barcelona teilzunehmen. Neben den QlikCommunity-Größen Rob Wunderlich, Bill Lay, Barry Harmsen und Oleg Troyansky, trafen sich gut 50 QlikView Spezialisten aus der ganzen Welt zu Vorträgen und regem Gedankenaustausch.

Masters Summit for QlikView

Die dreitägige Vortragsreihe hatte einen Schwerpunkt auf die QlikView Developer- und Designerrolle, mit dem Anspruch selbst erfahrenen Entwicklern noch die eine oder andere Neuigkeit beizubringen. Also durchaus schwere Kost, die uns aber durch das wundervolle Wetter in Barcelona versüßt wurde :-)

Die Themen waren:
  • Advanced Scripting: Vorstellung der QlikView Components durch Rob Wunderlich himself
  • Data Modeling: Barry Harmsen ging mit uns durch die typischen Datenmodell-Patterns für Fortgeschrittene inklusive Concat-Datenmodell, LinkTable, GenericKey und AsOf-Kalender (den ich bisher selbst immer Superkalender nannte) für Lagerbewegungen
  • Set Analysis: Oleg Troyansky über Alternate State, Bucketanalyse mit Rank und AGGR sowie Advanced Set Analysis
  • Effective Visualizations: Bill Lay's Gedanken über die Grundsätze von Stephen Few
  • QlikView Competency Center Best Practice: Diskussion des typischen 3-4 Schichten Modells in QlikView (Aufteilung QVD/Datenmodell/Layout). SVN Integration, sowie einige gute Gedanken zu Regressiontesting mit dem JMeter-Tool des QlikView Scalability Centers. 
  • Performance Tuning: Oleg Trojansky mit einer ausgezeichneten Erklärung wie QlikView Daten mittles bit-stuffed Pointers in den Symboltables ablegt.
Einzig der Vortrag über QlikView Server Administration inklusive  EDX-Integration, HTTP-Header Authentication und Ticketing schien mir zu wenig in die Tiefe zu gehen. Aber vielleicht ist das einfach meiner Historie als Expert Service Spezialist zu genau diesen Themen bei der QlikTech geschuldet. Dafür hatte unser Blog höchstpersönlich einen Auftritt beim MasterSummit: Das Development Team Easter Egg sorgte für allgemeine Erheiterung nach den anstrengenden Sessions!

Für all jene, die es zu dem Summit nach Barcelona nicht geschafft haben, und auch unseren hd Business Roundtable vergangenen Freitag bei Resch&Frisch in Wels verpasst haben, findet sich eine kleine aber feine Applikation mit einigen Tipps&Tricks auf unserem Accesspoint (auch zum Download).

Roland Vecera





Comment your .qvds - Zumindest bei Optimized Load!

Seit QlikView 10 ermöglicht das Feature "Comment & Tagging" Metainformationen auf Tabellen- und Feldebene zu hinterlegen. Der Entwickler des Datenmodells (zumeist aus der IT) kann somit zusätzliche Informationen an den QlikView Designer (zumeist Fachanwender) weitergeben.

Folgendes Skript hinterlegt die passenden Kommentare zu den Feldern des Kundenstamms.
Customer:
LOAD Firma,
    `Kunden-Code`,
    Land,
    Ort,
    PLZ,
    Region;
SQL  select
*
FROM Kunden;

Mapping_FieldComments:
Mapping
LOAD * INLINE [
    Field, Comment
    Firma, Company Long Name 
    Kunden-Code, Company Unique ID
    Land, Country Long Name from Customer Dimension
    Ort, City Long Name from Customer Dimension
    PLZ, Zip Code from Customer Dimension
    Region, Region Long Name
];

comment fields using Mapping_FieldComments;

Damit erhält der Benutzer am QlikView Frontend in der Tabellenvorschau und bei "Felder auswählen" die Feldbeschreibungen als Tooltip:


Bisher hatte ich verstanden, dass diese Kommentar-Information in der .qvw hinterlegt ist. Eleganter wäre es natürlich, wenn die Kommentare in der .qvd abgespeichert wären. Greift eine .qvw auf die Daten einer .qvd zu, so sollten auch die Kommentare in der Applikation zur Verfügung stehen. So hätte man die gleiche Feldbeschreibungen in jeder .qvw die auf diese .qvd aufsetzt!

Kommentar in .qvd gespeichert

Über einen Supportcase entdeckte ich indirekt folgende Information: Öffnet man die .qvd mit einem Texteditor (oder mit einem der netten QVD-Viewer die es mittlerweile gibt. zB EasyQlik QViewer), findet sich im XML-Header der .qvd-Datei doch tatsächlich ein Tag <Comment> das die Kommentarinformation für jedes Feld enthält!


Sehr nett! Die Frage nun: würde meine Idee mit der zentralen Speicherung der Feldbeschreibungen in .qvds auch funktionieren? Folgende 3 Tests habe ich durchgeführt:

1) Lädt man die .qvd UNOPTIMIZED, erscheinen die Kommentare leider nicht in der .qvw:

LOAD Firma, 
     [Kunden-Code], 
     Land, 
     Ort, 
     PLZ, 
     Region
FROM
Customer.qvd
(qvd) where 1=1;  //unoptimized



2) Lädt man die .qvd OPTIMIZED, bekommt man tatsächlich die Kommentare in der .qvw angezeigt.

LOAD Firma, 
     [Kunden-Code], 
     Land, 
     Ort, 
     PLZ, 
     Region
FROM
Customer.qvd
(qvd);  //optimized




3) Der BINARY-Load verhält sich lieder genauso wie der "UNOPTIMIZED"-Load. Die Feldkommentare werden in QV11.20SR3 nicht in die .qvw übernommen. Abhilfe schafft hier, indem man die .qvd als XML-Datei lädt und sich die Kommentare so manuell hinzufügt:

Binary [2_loadqvd_optimized.qvw];
//SET Variables here

Mapping_QvdFieldHeader:
Mapping
LOAD FieldName,
    Comment
FROM Customer.qvd (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]);


comment fields using Mapping_QvdFieldHeader;



Somit haben wir momentan ein "JEIN" als Antwort auf  die Frage einer zentralen Speicherung von Feldbeschreibungen in .qvds! Ich bin gespannt ob die Lücke in neuen ServiceReleases geschlossen wird. Für Fall 1) UNOPTIMIZED gibt es zumindest schon eine BugID!


Alle Beispiele zum Nachvollziehen und Ausprobieren finden sich unter: http://www.heldendaten.eu/blog/commentField_StoredInQVD.zip

QlikView Script: $(Must_Include) für externe Scripte

QlikView Scripte extern zu speichern ist aus vielen Gründen sinnvoll:

  • Config Dateien extern halten
  • Wiederkehrende Skriptteile nur einmal pflegen (zB KalenderScript, oder Libraries wie QlikView Components
  • Datenbank Connections extern halten, um zwischen Entwicklungs- und Produktivumgebung nicht manuell in die .qvw eingreifen zu müssen.
Der Qlikview Befehl für externe Skripte lässt sich im Skripteditor generieren und erzeugt für eine externe Datei "scriptfile.txt" folgenden Include-Befehl:
$(Include=.\scriptfile.txt);
So weit, so gut! Nur was passiert wenn die Datei scriptfile.txt nicht existiert (weil das File versehentlich in einem anderen Verzeichnis liegt, oder man sich vertippt hat)? Der erfahrende Qlikview Entwickler weiß: Es passiert gar nichts! Per Definition ignoriert QlikView das Fehlen des externen Skripts und läuft einfach mit dem restlichen Skript weiter! Im besten Fall bekommt man einen (irreführenden) Folgefehler, weil zb. die im include-File gepflegte Datenbank-Connection nicht geöffnet werden konnte. Die Fehlersuche kann dann aber häufig langwierig sein, da man zumeist nicht als erstes an das nicht-vorhandene Include-File denkt.

Ein ähnliches Verhalten kennt man aus PHP! Dort können externe Skripte mit "include" (entspricht der QlikView-Denke) oder mit "require" (wirft einen Fehler wenn die externe Datei nicht gefunden wurde) eingebunden werden.

Gibt es nun ein "require"-Verhalten in QlikView? Ja, gibt es - aber erstmal gut versteckt! Toni Kautto hat es als Erster in einem QlikCommunity-Forumthread folgende Lösung gepostet:
$(must_include=scriptFile.txt);
Testet man mit einem Script wie im Screenshot unterhalb, so wirft $(must_include) einen Fehler der klar zeigt, daß die Datei "doesNotExist.txt" vom must_include-Befehl nicht gefunden werden konnte!


In QV11.20 SR3 lässt sich der Befehl nun auch in der offiziellen Dokumentation finden. Damit können hoffentlich auch alle Sorgen (wie an anderen Stellen  in der QlikView Blogosphere geäußert) zerstreut werden, dass dieses Feature nicht offiziell supported ist!


Viel Spaß beim Abändern Ihrer existierenden Skripte & einen schönen Sommer!
Ihr heldendaten Team

Document Extension - Kommentare mit Link hinterlegen

QlikView bietet zu jedem Objekt in der Titelleiste ein Kommentar-Feld an. Dieses Feld ist sehr praktisch um kurze Erklärungen zu jedem Objekt zu verfassen. Wer aber komplexere Informationen hinterlegen will, würde vielleicht gerne auf eine Wiki-Seite mit detaillierter Dokumentation verweisen.

Leider interpretiert QlikView im Kommentarpopup keine Hyperlinks. Ein Klick auf das Kommentar-Popup schließt dieses, und man kehrt zu QlikView zurück. QlikView bietet uns im Full Browser/AJAX-Client ab Version 11 eine schöne Möglichkeit dieses Verhalten zu ändern: Document Extensions.

Document Extensions sind GUI-lose Modifizierungen, mit denen man das Verhalten des AJAX-Clients abändern kann. In der folgenden Document Extension wollen wir also anstatt das Popup zu schließen, lieber dem Hyperlink zu unserer Detaildokumentation folgen!

Für den Anwender

Für den Anwender kann das also wie folgt aussehen (eine Livedemo finden sie unter demo.heldendaten.net):

1) Normales QlikView Verhalten: Das Kommentar wird bei MouseOver als Tooltip angezeigt


 2)  Klickt man das Icon erscheint wie gewohnt das Popup!


3)  Ohne Document Extension würde sich beim Klick das Popup wieder schließen. Mit Document Extension öffnet sich der Link der im Kommentar unter spitzen Klammern steht: http://en.wikipedia.org/wiki/Bar_chart


Document Extension

Was ist passiert? Technisch läuft beim Öffnen der Applikation ein kleiner Javascript Code der sich zum Klick-Event des Kommentar-Popups hängt.

Qva.AddDocumentExtension('HD_ClickCommentExtension', function() {
       
    //Add Pointer-Cursor to class QvMessagePopup table 
    $("").appendTo("head");
    
    $("body").on("click", ".QvMessagePopup table", function(event){
        var comment = $(this).text();
        var URL = "";
        //extract URL from Comment 
        //Text between < >  is interpreted as URL
        URL = comment.substring(comment.indexOf('<') + 1,comment.indexOf('>'));
        //Check if it's a valid URL
        if(/^(http|https|ftp):\/\/[a-z0-9]+([\-\.]{1}[a-z0-9]+)*\.[a-z]{2,5}(:[0-9]{1,5})?(\/.*)?$/i.test(URL)){
          //Open new tab
          window.open(URL);
        }
        else
        { if (URL.length > 0)
            alert("Sorry, no URL");
        }
    });
  
});
Findet die Dokument-Extension im KommentarText eine URL in spitzen Klammern (<URL>), dann öffnet es beim Klick mit window.open() die Seite.

Wie bekommt man die Document Extension in die .qvw?

Eine Document Extension ist genauso ein .qar-Packet wie herkömmlichen Extensions. Ein Doppelklick auf die Extension führt die Installation im QVDeveloper durch.
Um die Document Extension in die .qvw hinzuzufügen, muß man den Menüpunkt "Eigenschaften des Dokuments|Tab Erweiterungen" aufrufen. Dort kann man die installierte Document Extension als "Aktive Erweiterung" definieren.


Damit funktioniert die Extension im WebView-Modus des QlikView Developers. Um die Extension für alle Benutzer am Server verfügbar zu machen, muss man sie am Server unter den Defaultpfad entpacken: C:\ProgramData\QlikTech\QlikViewServer\Extensions\Document\HD_ClickCommentExtension







Qlikview 11.20 SR2 unter Windows 2012

Alle die unseren QlikView RoundTable in Wien besucht haben, hatte ich ja schon erzählt, dass Windows 2012 einen schönen Vorteil mit sich bringt: Die 32GB MemoryGrenze der Windows 2008 R2 Standard-Edition ist gefallen! Windows 2012 Standard Edition erlaubt einen Arbeitsspeicher von bis zu 4TB (also ein ziemlich großer QlikView Server :-))

Memory-Obergrenzen Windows 2012 zu Windows 2008 R2


Wer also gerade über eine Erweiterung seines QlikView-Servers auf >= 64GB nachdenkt, oder eine Migration zu QV11 durchführen will, sollte mal einen Blick auf Windows 2012 werfen, bevor man unnötig Lizenzkosten in Windows 2008 Enterprise Edition investiert.

Mittlerweile ist auch QlikView 11.20 SR2 erschienen, das einen offiziellen Support für Windows 8, IE 10 und Windows 2012 bringt. Grund genug das wir uns eine entsprechende Testumgebung aufbauen. Unter folgendem Link finden Sie die Downloads von Oracle Virtualbox sowie einer fertigen .VHD-Datei von Microsoft Windows 2012 (mit einer 180 Tage Testversion).

Sind die Downloads erledigt, und die .VHD-Datei gemounted, muss man noch einige Einstellungen (Sprache, Admin-Passwort) am Windows 2012 vornehmen.

 Nach einem Neustart läuft dann Windows 2012, und wir können die QlikView Setups herunterladen.

Achtung: sowohl für Windows 8 als auch Windows 2012 gibt es hierfür eigene Installationspakete auf der QlikView Webseite. Siehe Screenshot unterhalb! Die Setups gibt es ausschließlich für 64bit!



Qlikview 11.20 SR2 Desktop


Die Installation von QlikView Desktop funktioniert anstandslos. Nach der Installation erscheint der QlikView-Button auf der neuen Kachel-Oberfläche. Startet man QlikView von dort, erscheint das gewohnte Look&Feel. Sowohl der normale Modus, als auch WebView scheinen zu funktionieren. Vorherige QlikView-Versionen hatten ja gerade in Kombination Internet Explorer 10 und Webview noch ihre Einschränkungen!


QlikView Developer

QlikView Developer im Webview Modus

QlikView 11.20 SR2 Server


Die Serverinstallation verlief ebenfalls problemlos. .NET 4.0 schien unter Windows 2012 bereits vorhanden, erforderte also keine extra Installation. Das einzig Nervige war mal wieder die Internet Explorer Enhanced Security: diese lässt sich aber wieder im (neugestaltetem) Server Manager deaktivieren:


Wie unter Windows 2008 erlaubt die Installation auch wieder die Wahl zwischen "Lokaler QlikView Administrator-Gruppe" und "Use digital certificates" (unter Win2003 gabe es diese Option ja nicht). Der Einfachheithalber blieb ich auch unter Windows 2012 bei der "QlikView Administrator" Gruppe. 


Der obligatorische Restart nach der Installation bleibt erhalten. Das Lizensieren von QlikView Server und QlikView Publisher über die QlikView Management Console ebenfalls.

Ruft man danach den Accesspoint auf, ist alles wie gehabt. Defaultmäßig öffnet der "Full Browser Version" (== AJAX) Client die Applikationen.


Einzig der About-Reiter zeigt uns, dass QlikView nun erfolgreich auf Windows 2012 läuft: Microsoft Windows NT 6.2.9200.0









Lessons Learned 2012 - Indexed Time Series Chart

Ende Mai wird es langsam spät für "Lessons Learned" aus dem Vorjahr, aber die "Indexed Time Series Chart" Darstellung wollte ich auf unserem Blog noch vorstellen :-).  Die konkrete Anforderung war: Man will die Umsatzentwicklung mehrerer Filialen prozentuell ab einem Stichtag X gegenüberstellen. So will der Benutzer ablesen, welche Filialen eine gute Umsatzentwicklung hingelegt haben, und welche hinterherhinken.

Ein Liniendiagramm mit absoluten Zahlen ist hier schwieriger zu interpretieren: eine direkte Gegenüberstellung von "umsatzstarken" Filialen zu kleineren Filialen lässt sich aufgrund der Skalierung schwer umsetzen. Das folgende Bild zeigt einen alten Datenbestand von Yahoo Finance aus dem Jahr 2009. Welche Aktie sich gut entwickelt hat ist kaum zu erkennen. Der Kurs der "Immofinanz" ist im Vergleich zur "Vienna Insurance" soviel niedriger, dass die Skalierung kaum eine Interpretation über die Aktienkursentwicklung zuläßt.


Eine bessere Darstellung für unsere Anforderung wäre, den Stichtag 01. Juli 2009 für alle Aktien mit 100% zu indizieren, und dann die prozentuelle Entwicklung über die Zeit zu betrachten. Die Kollegen von qvdesign haben dazu einen schönen Blogeintrag mit Beispiels .qvw anhand des "Big Mac Index" gebastelt.

Das Prinzip angewandt auf unsere Daten zeigt: Die Immofinanz hat sich im gewählten Zeitraum mit Abstand am Besten entwickelt.


Als Dimension dient hier das Datum und der "Name" der Aktie.  Technisch interessant ist aber vor allem die Formel die in QlikView benutzt wird:

sum(Close)/(sum(total <Name> if(Date=min(total Date),Close)))
Der Dividend sum(Close) ist noch klar: Close ist die Kennzahl die hier dargestellt werden soll.

Der Divisor ist der spannende Teil: Um aber pro Aktie/Filiale/Name immer auf den ersten Tag (= die 100%) referenzieren zu können muss man QlikView mit dem "total <Name>"-Qualifier aus der eigenen Chart-Dimension herausspringen lassen und dann mit "Date=min (total Date)" das erste Datum pro Name holen.



Alle meine bisherigen Lösungen für diese Art Darstellung hatten Einschränkungen, und sind entsprechend nicht zu empfehlen:

sum(Close)/firstsortedvalue(total<Name> Close,Date)
> Funktioniert nur, wenn es pro Tag genau einen Close-Wert gibt!

sum(Close)/sum({$<DateX={$(vMinDate)} >} if (SymbolX = Symbol,CloseX))
> Mit Set Anaylsis mußte ich die Faktentabelle nochmal als "Lookup" laden. Schon mit kleinen Datenmengen langsam.
Das Beispiel findet sich unter: http://content.heldendaten.eu/indexedtimechart.zip
PS: leider funktioniert das Datenladen von YahooFinance scheinbar nicht mehr! Deswegen dieser alte Datentopf!







QlikView - Hardware Server Settings For Best Performance



QlikView auf einem physischen Server zu betreiben ist die ideale Wahl. Diesen physischen Server dann auch noch richtig zu konfigurieren - das kann wichtig sein um das Maximum aus Ihrer gekauften Hardware herauszuholen.

Das QlikView Scalability Center hat dafür einen entsprechenden "Quick tip" veröffentlicht. Wie Sie sehen können, werden folgende BIOS-Settings empfohlen:
  • Hyperthreading zu deaktiveren, und 
  • Node Interleaving Node Interleaving zu aktivieren (das bedeutet NUMA deaktivieren)
Update 18.06.2013

QlikTech hat folgende Information bezüglich Hyperthreading und NUMA gepostet:
Hyper-threading
Disabled (most architectures), Enabled (2 socket Intel E5-XXXX architectures)
Since 11.2 SR2: QVS is defaulted to an automated state that detects NUMA status and set EnableNumaRoundRobin setting in accordance to recommendations.

http://community.qlikview.com/docs/DOC-2362
Häufig haben wir aber gar nicht den Zugriff auf den Server, da dieser in einem Rechenzentrum steht. Wie also die aktuellen BIOS-Einstellungen herausfinden? Microsoft bietet mit  Windows SysInternal CoreInfo ein kleines, aber feines Commandline-Tool wo man diese Informationen auslesen kann:




Mein Laptop zb. hat Hyperthreading aktiv. Wäre er ein QlikView Server, so sollte ich es im BIOS deaktivieren. Läßt sich Node Interleaving im BIOS nicht aktivieren, gibt es einen Software-Switch für die QVS Settings.ini (siehe "Quick tip" ):

For some servers there is no support for disabling NUMA. Since QV10 SR4 and QV11 any version there is a soft switch within QV that can be used to remove/reduce performance penalty from NUMA. It is recommended to use this setting for machines that does not have support for disabling NUMA.
The setting is not in the QMC and must be set in the Settings.ini file as a [Settings 7] entry
EnableNumaRoundRobin=0 is defaulted and means disabled functionality.

Also bevor Sie das nächste Mal Stundenlang Skripte und Formeln optimieren, stellen Sie bitte zuerst sicher, daß Ihr physischer Server optimal konfiguriert ist!  Der Quick Tip wird öfter mal upgedatet - wer also am Laufenden gehalten werden möchte, einfach in der Qlikcommunity anmelden und die "Email-Benachrichtigung" für diese Seite aktivieren!


Übrigens:
Virtualisierung funktioniert häufig gut, manchmal aus diversen Gründen (Memory Balloning, zu wenig garantierte CPU-Zeit) nicht, bringt aber sicherlich IMMER Performance-Einbüßen durch den zusätzlichen Overhead mit sich. QlikTech hat aber prinzipiell nichts gegen Virtualisierung. Hier  findet sich auf der VMWare-Homepage sogar ein QlikTech Support Statement.

QlikView Easter Egg - Das Entwicklungsteam

Bevor sich alle auf die Suche nach ihren Ostereiern begeben, hier noch schnell ein Beitrag über ein schönes Easter Egg im QlikView Developer: Seit der ersten QlikView-Version gibt es in jedem Release ein verstecketes Bild der QlikView-Entwicklungsmannschaft aus Schweden.

Die ganz alten Versionen finden sich leider nicht mehr zum Download, aber folgende Bilder lassen sich seit der Version 7 finden:

Version 7 & 8 - scheinbar das Bild aus Qlikview 6

Version 9 und aufwärts

Der CTO von QlikTech hat sogar einmal erzählt, dass in der ersten QlikView-Version dieses Bild mehr Platz benötigte, als der gesamte eigentliche Programmcode von QlikView ;). Mittlerweile paßt das Entwicklungsteam wohl nicht mehr auf ein einzelnes Foto - sonst wäre eine Aktualisierung in QlikView 11 doch dringend notwendig :)

Wenn Sie es selbst ausprobieren wollen, bauen Sie einfach eine Textbox mit folgenden Code
qmem://<bundled>/DevTeam.jpg
und wählen Sie unter Eigenschaften|Allgemein|Representation den Eintrag Bild! Oder Sie laden sich die Applikation hier herunter! 

In diesem Sinne wünschen wir frohe Ostern und viel Spaß beim Eiersuchen!
Ihr heldendaten Team

PS: Wer übrigens ein Freund des alten Microsoft Office "Clippy"-Büroklammerngehilfen ist, dem sei folgender Blogbeitrag ans Herz gelegt. Auch der findet sich nämlich gut versteckt in Ihrem QlikView Developer.

Stücklisten in QlikView - Recursive SQL

Heute würde ich mich gerne den Themen Stücklisten (oder bill of materials (BOM)) , QlikView und Recursive SQL-Statements widmen. Exemplarisch gehe ich hierbei von einer Stückliste für ein Auto aus:

Wie wir sehen können wird das Material "Schraube" in mehreren Bauteilen benötigt.  Insgesamt gibt es vier Subtrees die verschiedene Mengen an Schrauben benötigen. Um also insgesamt das Auto bauen zu können, benötigt man in unserem Beispiel 96 Schrauben.

Relational sind Stücklisten häufig in einer Parent-Child Struktur abgespeichert, da Stücklisten ja beliebig tiefe Verschachtelungen (Ebenen) haben können.


In QlikView wünscht man sich aber oft eine flache Darstellung mit einer benamten Spalte pro Ebene:


Naheliegend wäre es jetzt den QlikView Hierarchy Load an dieser Stelle einzusetzen. Dieser Befehl wurde in Qlikview 8.5 eingeführt, um zB. rekursive Vertriebshierarchien in die oben gezeigte Darstellung  zu bringen.

Führt man den Hierarchy-Befehl auf das gezeigte Datenset aus, liefert der Befehl  aber leider kein vollständiges Ergebnis. Die Knoten für die Subtrees:

  • Auto - Karosserie - Schraube
  • Auto - Karosserie - Schraube - Schraubenmutter
fehlen im Ergebnis. Entsprechend wäre in einer fertigen Applikation auch die Menge (Qty) für die Schrauben zu niedrig.

Warum ist das der Fall? QlikView's Hierarchy Befehl geht davon aus, dass jeder Knoten genau einen Vorgänger hat. Das klappt gut etwa bei einer Vertriebshierarchie: jeder Vertriebsmitarbeiter hat einen Teamleiter, jeder Teamleiter einen Gebietsleiter, usw. Bei Stücklisten ist der Fall schwieriger: Die Schraube beinhaltet zwar immer eine Schraubenmutter, die Schraube selbst wird aber an den unterschiedlichsten Stellen eingesetzt (Karosserie, Reifen, Zylinderdichtung und beim Auto selbst). Die Schraubenmutter kann auch ohne Schraube eingesetzt werden (im Beispiel direkt beim Auto, bei der Zündkerze).  QlikView scheint zwar noch einige Ebenen zu schaffen, aber irgendwann lässt es einfach Knoten weg. Leider ohne Fehlermeldung. Das Verhalten ist aber nach Nachfrage beim Support "Working as Designed".

Wie kann man nun die Stückliste trotzdem in QlikView einlesen? Der SQL Standard 1999 ist mittlerweile in vielen großen Datenbanksystemen implementiert, und bietet rekursive SQL Funktionen. Wenn man also das Auflösen der Stückliste auf die Datenbank auslagert, kann man die Daten in QlikView einlesen:

with rvaRec (ebene, path, Parent, Child, Quantity,QuantitySubTree,QuantitySubTreeValue) as (
 select 
  1 as ebene, 
  cast('|'+ Child as varchar(1000)) as path, 
  Parent,  
  Child, 
  Quantity, 
  cast(Quantity as varchar(1000)) as QuantitySubTree, 
  CAST(Quantity as float) as QuantitySubTreeValue 
 from [TestRVA].[dbo].[Stueckliste] where Parent is null
 union all
 (
 select 
  ebene+1 as ebene, 
  cast(path+'|'+c.Child as varchar(1000)) as path, 
  c.Parent as Parent, 
  c.Child as Child, 
  c.Quantity as Quantity, 
  cast(QuantitySubTree+'|'+cast(c.[Quantity] as varchar(50)) as varchar(1000)) as QuantitySubTree, 
  CAST(QuantitySubTreeValue*c.[Quantity] as float) as QuantitySubTreeValue  
 from rvaRec p, [TestRVA].[dbo].[Stueckliste] c
 WHERE p.Child=c.Parent
 )
)
select ebene, path, Parent,Child, Quantity,QuantitySubTreeValue,QuantitySubTree from rvaRec order by ebene,path;


Das Statement oberhalb liefert das Ergebnis für die Auto-Stückliste. Das Feld path zeigt durch das Trennzeichen '|' getrennt die flache Darstellung der Stückliste an. Stellt man das Ergebnis des QlikView Hierarchy-Load und des rekursiven SQL Statements gegenüber, so sieht man, dass das SQL-Statement die  zwei Subtrees:

  • Auto - Karosserie - Schraube
  • Auto - Karosserie - Schraube - Schraubenmutter
 korrekterweise im Ergebnis anzeigt.



Mit diesem Ergebnis kann nun in QlikView weitergearbeitet werden. Das Feld path ist zwar noch nicht optimal um es in Qlikview zu benutzen - für die Darstellung am Frontend soll das Feld path in die einzelnen Ebenen (== Spalten) zerteilt werden - aber da kann man sich mit QlikView Boardmitteln helfen.

Um ein Feld in QlikView zu zerteilen, gibt es den selten genutzten Load From_Field Befehl:

load
*
From_Field(Tablename,path)
(txt,utf8,explicit labels,delimiter is '|', msq);
Mit dem Parameter delimiter is '|' gibt man das Trennzeichen des bereits geladenen Feld path an. Für jede Ebene generiert QlikView nun ein Feld in der Syntax @Ebene (also @1, @2, @3, usw). Um zum endgültigen Ergebnis in QlikView zu kommen muss man also entsprechend die generierten Felder sinnvoll umbenennen und zu einer Tabelle zu joinen (ähnlich dem Generic-Load).

Die fertige Applikation sieht dann wiefolgt aus: die Felder Nodename1 bis Nodename7 entsprechen dem Feld path aufgesplittet nach dem Trennzeichen. Als korrekte Quantity für das Material "SCHRAUBE" wird in der Pivottabelle 96 angezeigt. Die Pivottabelle zeigt die Baumdarstellung mit ausmultiplizierten Mengen!


 Ein komplettes Skriptbeispiel, sowie ein Beispiel für die Limitation des Hierarchy-Loads finden Sie hier!



heldendaten GmbH,2017