Lektion 6 - Skript Tips und Tricks - b.eye

Übersicht

Diese Kurzlektion beschäftigt sich mit typischen Fragestellungen aus der Praxis und deren Lösungsvorschläge.

Markieren von Dubletten

Oft kommt es in der Praxis vor, dass “Dubletten” für eine Information existieren und Sie allerdings nur den “neuesten” Datensatz berücksichtigen wollen. Für diese Problemstellung bietet QlikView im Skript so genannte “Interrecord-Funktionen” zur Verfügung. Diese erlauben während des Ladens einer Quelle auf vorgehende oder nachfolgende Datensätze zuzugreifen. Die Lösung für unser Problem sähe wie folgt aus:

//im ersten Schritt laden wir die Tabelle ohne "Veränderung"
RawTable:
LOAD 
    *
FROM [$(PATH_DATASTAGING)TestSource.qvd];

//danach sortieren wir sie nach unserem Dubletten Schlüssel und z.B. einer Zeitinfo
SortedTable:
NOCONCATENATE LOAD
    *
RESIDENT RawTable
ORDER BY %DublettenId, %Date;

//um keine Zirkelbezüge zu bekommen, entfernen wir die alte Tabelle
DROP TABLE RawTable;

//danach vergleichen wir den Schlüssel und markieren unseren Datensatz
FinalTable:
NOCONCATENATE LOAD
    *
    ,IF(PREVIOUS(%DublettenId) = %DublettenId,0,1) AS FLAG_VALID
RESIDENT SortedTable;

//als letztes entfernen wir wieder die Tabelle um keine Zirkelbezüge zu bekommen
DROP TABLE SortedTable;

Auflösen von “multiplen” Schlüsseln

In der Praxis kommt es oft vor, dass eine Beziehung zwischen 2 Tabellen auf mehr als nur einem Feld besteht. Um die Performance zu verbessern und das Datenmodell “sauber” zu halten, lohnt es sich in diesen Fällen mit einem einfachen Trick die Schlüssel zu kombinieren.

//Ausgangssituation
Tabelle1:
LOAD
 %KostenstelleId,
 %ProduktId,
 ...
RESIDENT Tabelle1;

Tabelle2:
LOAD
 %KostenstelleId,
 %ProduktId,
 ...
RESIDENT Tabelle2;

//für die Performance machen wir einen verbundenen Schlüssel
Tabelle1:
LOAD
  %KostenstelleId & 'xx' & %ProduktId AS %Verbindung,
  ..

Klassifizieren von Daten

ApplyMap Funktion

Informationen auf Basis einer “Karte” zuordnen. Dabei gelten die folgenden Anforderungen an die Karte:

Die Zuordnungstabelle besteht aus 2 Spalten, in der Ersten steht der Vergleichswert, in der Zweiten der spätere “Klassenwert”.
Die 2 Spalten müssen benannt sein, wobei die Benennung später keine Relevanz hat. Vergessen Sie die Benennung bei z.B. einer Inline-Tabelle, wird der erste Wert nie einen Treffer liefern.
Beim Gebrauch der Zuordnungstabelle werden die Werte eines bestimmten Feldes verglichen, bei einem Treffer wird der Zuordnungswert “ersetzt” ansonsten bleibt der ursprüngliche Wert bestehen.

Wenn Sie sich Fragen, wieso wir diese Daten nicht mittels Join ins Datenmodell ziehen, so liegt der Grund in Punkt 3 der gerade aufgeführten Punkte. Ein JOIN würde uns die Tabelle immer erweitern und ein “behalten” der Originalwertes ist nicht möglich.

Folgendes Bsp. mit der Verwendung von Inline-Tabellen:

//als erstes bauen wir unsere inline Tabelle
Rabattfristen:
LOAD * INLINE
[
  Rabatt, Frist
  VZ1J, 1 Jahr
  VZ2J, 2 Jahre
  B1J, 1 Jahr
];

//danach bauen wir uns die Mapping Tabelle
Rabatt_Mapping:
MAPPING LOAD
  Rabatt,
  Frist
RESIDENT Rabattfristen;

//tabelle löschen nicht vergessen
DROP TABLE Rabattfristen;

//die so erzeugte Karte kann jetzt auf die Bestellungen gelegt werden
Bestellungen:
LOAD 
  *,
  applymap('Rabatt_Mapping',Discount,Discount) AS RabattFrist
;
SQL SELECT * FROM Bestellungen;

IntervallMatch Funktion

Klassifizieren von Werten auf Basis eines Unter- und Oberwertes. Diese Funktion kann allerdings auch für komplexere Fragestellungen wie z.B. eine Zeitreihenanalyse genutzt werden. Um das Prinzip hinter der Funktion zu verstehen, ist es allerdings viel einfacher wir nehmen das Beispiel einer Immobilienagentur, die auf Basis der Größe von Wohnungsflächen eine Klassifizierung festlegen möchte.

//zuerst laden wir die Wohnungen
Wohnungen:
LOAD 
 WohnungsId,
 Wohnfläche,
 Postleitzahl;
SQL SELECT * FROM Wohnungen WHERE Wohnfläche > 0;

//Laden der Größengruppen
Sizegroups:
LOAD * INLINE
[
 VonQM, BisQM, SizeGroup
 0, 10, klein
 10.0001, 40, mittel
 40.0001, 100, gross
 100.0001, 99999, gigantisch 
];

//jetzt ordnen wir die Größengruppen auf die Wohnfläche zu
IVMatch:
IntervalMatch(Wohnfläche)
LOAD DISTINCT VonQM, BisQM RESIDENT Sizegroups;

Dies kann auch auf Ereignisse angewendet werden und führt im Hintergrund zu folgenden Datenmodel:

Performance – Verketten von Bewegungstabellen

Das Verketten von Bewegungstabellen – auch unterschiedlicher Spaltenanzahl und Felder – bringt, solange alle Schlüsselfelder “überlappen” einen großen performance Vorteil. Solange alle Schlüsselfelder enthalten sind, ist dies auch unbedenklich für das spätere Drillen ins Detail. Sollten allerdings nicht alle Schlüsselfelder überlappen, so geht dies zwar auch, aber beim Drillen kann es zu “Detailbrüchen” kommen, für die man sich dann unter Umständen “erklären” darf.

Achtung: QlikView fasst Tabellen mit der gleichen Struktur automatisch zusammen. Dies kann zu erheblichen Problemen führen, bietet allerdings auch viele Chancen. Wie Sie das Verhalten bewusst steuern zeige ich Ihnen im folgenden Beispiel!

//automatische Zusammenführung
LOAD a, b, c FROM Tabelle1.csv;
LOAD a, c, b FROM Tabelle2.csv;

//manuelle Zusammenführung
LOAD a, b, c FROM Tabelle1.csv;
CONCATENATE LOAD a, c FROM Tabelle2.csv;

//manuelle "nicht" Zusammenführung
LOAD a, b, c FROM Tabelle1.csv;
NOCONCATENATE LOAD a, b, c FROM Tabelle2.csv;

Im letzten Fall sollten Sie mit der Tabelle weiterarbeiten und verändern, da die hohe Gefahr besteht, dass Ihr Datenmodel “platzt”.

Ausführen einer Funktion für jede Zeile einer Tabelle

QlikView erlaubt Ihnen auch im Skript bestimmte Funktionen selbst zu erstellen und diese auch mit Parametern aufzurufen. Das einfachste Beispiel ist, dabei das Schreiben einer Nachricht in eine Log-Datei. (Geht nur um die Technik, nicht den Sinn!)

//Definition der Funktion
SUB Qvc.Log (_msg, _level)
 ...
END SUB;

//hier laden wir die Ausgangstabelle
Personen:
LOAD 
  id,
  name
FROM Tabelle1.csv;

//wieviele Zeilen hat die Tabelle
LET varNoOfRecords = NoOfRows('Personen');

//erstellen einer leeren Ergebnistabelle
LOAD 
 'F' as DummyField
AUTOGENERATE (1);

//start einer Schleife durch die Tabelle
FOR ii = 0 TO $(varNoOfRecords) 

 LET varName = peek('name',$(ii),'Personen');
 Qvc.Log('Hallo $(varName)');

NEXT;