Lektion 6 – Skript Tips und Tricks
Ü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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
//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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
//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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
//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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
//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!
|
1 2 3 4 5 6 7 8 9 10 11 |
//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; |
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!)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
//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; |
TODO: Beispiel für das durchlaufen einer Tabelle und ausführen einer Funktion mit enthaltenen Werten.