Einordnung in Intervalle und iterative Ladevorgänge

Der Intervalmatch-Zusatz einer LOAD- oder SELECT-Anweisung wird verwendet, um diskrete numerische Werte mit einem oder mehreren numerischen Intervallen zu verknüpfen. Diese leistungsfähige Funktion kann beispielsweise in Produktionsumgebungen verwendet werden.

Verwenden des IntervalMatch()-Zusatzes

Der grundlegendste Intervallabgleich ergibt sich bei einer Liste mit Zahlen oder Daten (Ereignissen) in einer Tabelle und einer Liste mit Intervallen in einer zweiten Tabelle. Das Ziel ist die Verlinkung der beiden Tabellen. In der Regel handelt es sich dabei um eine n:n-Beziehung, d. h. einem Intervall können viele Datumswerte angehören und ein Datum kann vielen Intervallen angehören. Um diese aufzulösen, müssen Sie als Verbindung zwischen den beiden ursprünglichen Tabellen eine Brückentabelle erstellen. Dazu gibt es verschiedene Möglichkeiten.

Am einfachsten lässt sich dieses Problem in Qlik Sense lösen, indem der Zusatz IntervalMatch() vor einer LOAD- oder einer SELECT-Anweisung verwendet wird. Die LOAD/SELECT-Anweisung darf nur zwei Felder enthalten, und zwar die Felder From und To, welche die Intervalle definieren. Der Zusatz IntervalMatch() generiert daraufhin alle Kombinationen zwischen den geladenen Intervallen und einem zuvor geladenen numerischen Feld, das als Parameter für den Zusatz festgelegt wurde.

Gehen Sie folgendermaßen vor:

1. Erstellen Sie eine neue App und geben Sie ihm einen Namen.
2. Fügen Sie einen neuen Skriptabschnitt im Dateneditor hinzu.
3. Rufen Sie den Abschnitt Events auf.

4. Klicken Sie im rechten Menü unter DataFiles auf Daten auswählen.

5. Laden Sie Events.txt hoch und wählen Sie die Datei aus.
6. Klicken Sie im Fenster Daten auswählen aus auf Skript einfügen.
7. Laden Sie Intervals.txt hoch und wählen Sie die Datei aus.
8. Klicken Sie im Fenster Daten auswählen aus auf Skript einfügen.
9. Nennen Sie im Skript die erste Tabelle Events und die zweite Tabelle Intervals.
   
10. Fügen Sie am Ende des Skripts IntervalMatch hinzu, um eine dritte Tabelle zu erstellen, welche die beiden ersten Tabellen verbindet:

BridgeTable:
IntervalMatch (EventDate)
LOAD distinct IntervalBegin, IntervalEnd
Resident Intervals;

11. Ihr Skript sollte folgendermaßen aussehen:

Events:
LOAD
    EventID,
    EventDate,
    EventAttribute
FROM [lib://DataFiles/Events.txt] 
(txt, utf8, embedded labels, delimiter is '\t', msq);
 
Intervals:
LOAD
    IntervalID,
    IntervalAttribute,
    IntervalBegin,
    IntervalEnd
FROM [lib://DataFiles/Intervals.txt] 
(txt, utf8, embedded labels, delimiter is '\t', msq);
 
BridgeTable:
IntervalMatch (EventDate)
LOAD distinct IntervalBegin, IntervalEnd
Resident Intervals;

12. Klicken Sie auf Daten laden.
13. Öffnen Sie die Datenmodellansicht. Das Datenmodell sieht folgendermaßen aus:



Das Datenmodell enthält einen zusammengesetzten Schlüssel (die Felder IntervalBegin und IntervalEnd), der sich als synthetischer Qlik Sense-Schlüssel manifestiert.

Die grundlegenden Tabellen sind:

• Die Events-Tabelle enthält genau einen Datensatz pro Ereignis.
• Die Intervals-Tabelle enthält genau einen Datensatz pro Intervall.
• Die Brückentabelle, die genau einen Datensatz pro Kombination aus Ereignis und Intervall enthält und die beiden vorherigen Tabellen verlinkt.

Beachten Sie, dass ein Ereignis verschiedenen Intervallen angehören kann, wenn sich die Intervalle überschneiden. Einem Intervall können selbstverständlich auch verschiedene Ereignisse angehören.

Dieses Datenmodell ist insofern optimal, da es normalisiert und kompakt ist. Die Tabelle Events und die Tabelle Intervals bleiben beide unverändert und enthalten die ursprüngliche Anzahl an Datensätzen. Alle Berechnungen von Qlik Sense, die für diese Tabellen ausgeführt werden, beispielsweise Count(EventID), funktionieren und werden korrekt evaluiert.

Verwenden einer While-Schleife und iterativer Ladevorgänge IterNo()

Beinahe dieselbe Brückentabelle lässt sich mithilfe einer While-Schleife und IterNo() erreichen. Hierbei werden zwischen der unteren und oberen Grenze des Intervalls abzählbare Werte erstellt.

Eine Schleife in der LOAD-Anweisung lässt sich mithilfe einer While-Schleife erstellen. Hier ein Beispiel:

Eine derartige LOAD-Anweisung wird als Schleife für jeden Datensatz ausgeführt und immer wieder geladen, solange die Formel in der While-Bedingung zutrifft. Die Funktion IterNo() liefert "1" für die erste Iteration, "2" für die zweite usw.

Für die Intervalle verfügen Sie über einen Primärschlüssel, die IntervalID, der einzige Unterschied im Skript ergibt sich deshalb aus der Art und Weise, wie die Brückentabelle erstellt wird:

Gehen Sie folgendermaßen vor:

1. Ersetzen Sie die bestehenden Bridgetable-Anweisungen durch das folgende Skript:

BridgeTable:
LOAD distinct * Where Exists(EventDate);
LOAD IntervalBegin + IterNo() - 1 as EventDate, IntervalID
    Resident Intervals
    While IntervalBegin + IterNo() - 1 <= IntervalEnd;

2. Klicken Sie auf Daten laden.
3. Öffnen Sie die Datenmodellansicht. Das Datenmodell sieht folgendermaßen aus:



Generell ist die Lösung mit den drei Tabellen die beste, weil dadurch eine n:n-Beziehung zwischen Intervallen und Ereignissen möglich ist. Häufig ist jedoch auch bekannt, dass ein Ereignis nur zu einem einzigen Intervall gehören kann. In einem solchen Fall ist die Brückentabelle eigentlich nicht erforderlich. Die IntervalID kann direkt in der Ereignistabelle gespeichert werden. Es gibt verschiedene Möglichkeiten, dies zu erreichen, aber am hilfreichsten ist es, Bridgetable mit der Tabelle Events zu verbinden.

4. Fügen Sie am Ende Ihres Skripts folgendes Skript hinzu:

Join (Events)
LOAD EventDate, IntervalID
Resident BridgeTable;
 
Drop Table BridgeTable;

5. Klicken Sie auf Daten laden.
6. Öffnen Sie die Datenmodellansicht. Das Datenmodell sieht folgendermaßen aus:

Offene und abgeschlossene Intervalle

Ob ein Intervall offen oder abgeschlossen ist, hängt von den Endpunkten ab, und zwar davon, ob diese im Intervall eingeschlossen sind oder nicht.

• Sind die Endpunkte eingeschlossen, handelt es sich um ein abgeschlossenes Intervall:

[a,b] = {x ∈ ℝ ∣ a ≤ x ≤ b}

• Sind die Endpunkte nicht eingeschlossen, handelt es sich um ein offenes Intervall:

]a,b[ = {x ∈ ℝ ∣ a < x < b}

• Ist ein Endpunkt abgeschlossen, handelt es sich um ein zur Hälfte geöffnetes Intervall:

[a,b[ = {x ∈ ℝ ∣ a ≤ x < b}

Für den Fall, dass sich die Intervalle überschneiden und eine Zahl zu mehreren Intervallen gehören kann, sind normalerweise geschlossene Intervalle erforderlich.

In bestimmten Fällen sind überlappende Intervalle jedoch möglicherweise nicht erwünscht und eine bestimmte Zahl soll nur einem bestimmten Intervall angehören. Daher ergibt sich ein Problem, wenn ein Punkt das Ende eines Intervalls und zugleich der Beginn des nächsten ist. Eine Zahl mit diesem Wert würde beiden Intervallen zugewiesen werden. Aus diesem Grund sind zur Hälfte geöffnete Intervalle wünschenswert.

Eine praktische Lösung dieses Problems ist, eine geringe Zahl vom Endwert aller Intervalle abzuziehen. Auf diese Weise werden geschlossene, aber nicht überlappende Intervalle erstellt. Wenn es sich bei den Zahlen um Daten handelt, lässt sich dies am einfachsten mithilfe der Funktion DayEnd() erzielen. Diese gibt die letzte Millisekunde des Tages zurück:

Sie können auch einen kleinen Betrag manuell abziehen. Beachten Sie dabei aber, dass der abgezogene Betrag nicht zu klein ist, da die Operation auf 52 signifikante Binärstellen (14 Dezimalstellen) gerundet wird. Wenn Sie einen zu kleinen Betrag verwenden, ist der Unterschied nicht signifikant, wodurch wieder die ursprüngliche Zahl herangezogen wird.