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.
+800x600+%E2%80%93+GIMP_2013-11-12_15-02-22.png)
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:
Liest man das Skript nach den genannten Regeln, würde man folgendes Join-Verhalten erwarten:
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:
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:
Um aus der ursprünglichen Faktentabelle nicht "versehentlich" Zeilen zu entfernen, benötigen wir somit eine Zwischentabelle. Das Skript sieht dann folgendermaßen aus
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:
Auch hier hilft wieder der Umweg über eine temporäre "DISTINCT"-Tabelle
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
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.
+800x600+%E2%80%93+GIMP_2013-11-12_15-02-22.png)
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).
+800x600+%E2%80%93+GIMP_20_2013-11-12_15-26-32.png) |
| 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 |
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
