Einordnung diskreter Werte in Intervalle

Das intervalmatch-Präfix einer LOAD- oder SELECT-Anweisung wird verwendet, um diskrete numerische Werte mit einem oder mehreren numerischen Intervallen zu verknüpfen. Nachfolgend wird diese Funktion am Beispiel eines Produktionsprozesses erläutert.

Intervalmatch-Beispiel

Bitte betrachten Sie die zwei nachstehenden Tabellen. Die erste Tabelle zeigt Bearbeitungsstart und -ende verschiedener Aufträge. Die zweite Tabelle enthält punktuelle Ereignisse. Wie lässt sich nun ein Zusammenhang zwischen den Tabellen herstellen, in dem Sinn, dass erkennbar wird, welche Aufträge von welchen Ereignissen betroffen waren?

Tabelle OrderLog
Starten Sie . Ende Reihenfolge
01:00 03:35 A
02:30 07:58 B
03:04 10:27 C
07:23 11:43 D
Tabelle EventLog
Uhrzeit Ereignis Kommentar
00:00 0 Beginn von Schicht 1
01:18 1 Bandstopp
02:23 2 Wiederanlauf des Bands 50 %
04:15 3 Bandgeschwindigkeit 100 %
08:00 4 Beginn von Schicht 2
11:43 5 Produktionsende

Laden Sie zuerst die beiden Tabellen wie üblich und verknüpfen Sie dann das Feld Time mit den durch die Felder Start und End definierten Intervallen:

SELECT * from OrderLog;

SELECT * from EventLog;

Intervalmatch (Time) SELECT Start,End from OrderLog;

Sie können jetzt wie folgt eine Tabelle in Qlik Sense erstellen:

Tabelle, deren Feld „Uhrzeit“ mit den von „Start“ und „Ende“ definierten Intervallen verknüpft ist
Uhrzeit Ereignis Kommentar Reihenfolge Start Ende
0:00 0 Beginn von Schicht 1 - - -
1:18 1 Bandstopp A 1:00 3:35
2:23 2 Wiederanlauf des Bands 50 % A 1:00 3:35
4:15 3 Bandgeschwindigkeit 100 % B 2:30 7:58
4:15 3 Bandgeschwindigkeit 100 % C 3:04 10:....
8:00 4 Beginn von Schicht 2 C 3:04 10:....
8:00 4 Beginn von Schicht 2 D 7:23 11:....
11:43 5 Produktionsende E 7:23 11:.....

Jetzt ist offensichtlich, dass hauptsächlich Auftrag A vom Stopp der Produktionslinie betroffen war. Auftrag B und C wurden ebenfalls durch die verminderte Geschwindigkeit der Produktionslinie beeinträchtigt. Nur die Aufträge C und D wurden teilweise von Shift 2 bearbeitet.

Beachten Sie bei Verwendung von intervalmatch Folgendes:

  • Vor der intervalmatch-Anweisung muss das Feld mit den diskreten Werten (Time im obigen Beispiel) bereits in Qlik Sense eingelesen worden sein. Die intervalmatch-Anweisung liest dieses Feld nicht aus der Datenbanktabelle!
  • Die Tabelle, die durch die intervalmatch LOAD- oder die SELECT-Anweisung geladen wird, muss immer genau zwei Felder enthalten (Start und End im obigen Beispiel). Um Verknüpfungen mit anderen Feldern zu ermöglichen, muss das Feld mit den diskreten Werten zusammen mit anderen Feldern in einer separaten LOAD- oder SELECT-Anweisung (die erste SELECT-Anweisung im obigen Beispiel) gelesen werden.
  • Die Intervalle sind immer abgeschlossen. Das heißt, dass die Grenzwerte in den Intervallen enthalten sind. Nicht-numerische Werte als Intervallgrenzen führen dazu, dass das Intervall nicht berücksichtigt wird (undefiniert). Bei NULL-Werten als Intervallgrenzen ist das Intervall unbegrenzt (unendlich groß).
  • Wenn sich die Intervalle überschneiden, werden die Werte jedem passenden Intervall zugeordnet.

Behebung von Problemen mit sich langsam verändernden Dimensionen mit der erweiterten Syntax von intervalmatch

Die Syntax der erweiterten intervalmatch-Anweisung dient dazu, ein häufig vorkommendes Problem zu lösen: sich langsam verändernde Dimensionen in den Datenquellen.

Beispiel:

SET NullInterpret='';

 

IntervalTable:

LOAD Key, ValidFrom, Team

FROM 'lib://dataqv/intervalmatch.xlsx' (ooxml, embedded labels, table is IntervalTable);

 

Key:

LOAD

Key,

ValidFrom as FirstDate,

date(if(Key=previous(Key),

previous(ValidFrom) - 1)) as LastDate,

Team

RESIDENT IntervalTable order by Key, ValidFrom desc;

 

drop table IntervalTable;

 

Transact:

LOAD Key, Name, Date, Sales

FROM 'lib://dataqv/intervalmatch.xlsx' (ooxml, embedded labels, table is Transact);

 

INNER JOIN intervalmatch (Date,Key) LOAD FirstDate, LastDate, Key RESIDENT Key;

Die nullinterpret-Anweisung wird nur benötigt, wenn die Daten aus einer externen Tabellendatei eingelesen werden, denn dort sind fehlende Werte als leere Strings und nicht als NULL-Werte definiert.

Das Einlesen der Daten von IntervalTable liefert folgende Tabelle:

Table with data loaded from IntervalTable
Key FirstDate Team
000110 2011-01-21 Southwest
000110 - Northwest
000120 - Northwest
000120 2013-03-05 Southwest
000120 2013-03-05 Northwest
000120 2013-03-05 Southwest

Die nullasvalue-Anweisung ermöglicht das Mapping von NULL-Werten zu den aufgelisteten Feldern.

Nun werden die Attributfelder Key, FirstDate, LastDate mithilfe der previous- und order by-Anweisungen generiert. Anschließend wird die Tabelle IntervalTable gelöscht und durch diese Schlüsseltabelle ersetzt.

Das Einlesen der Daten von Transact liefert folgende Tabelle:

Tabelle mit aus Transact geladenen Daten
Schlüssel Name Datum Umsatz
000110 Spengler Aaron 2009-08-18 100
000110 Spengler Aaron 2009-12-25 200
000110 Spengler Aaron 2011-02-03 300
000110 Spengler Aaron 2011-05-05 400
000120 Ballard John 2011-06-04 500
000120 Ballard John 2013-01-20 600
000120 Ballard John 2013-03-10 700
000120 Ballard John 2013-03-13 800
000120 Ballard John 2013-09-21 900

Durch die intervalmatch-Anweisung, der die inner join-Anweisung vorausgeht, wird der Schlüssel durch einen neuen, synthetisch generierten Schlüssel ersetzt, der mit der Tabelle Transact verknüpft wird. Dadurch entsteht die folgende Tabelle:

Tabelle mit dem intervalmatch-Befehl und vorangestelltem inner join
Schlüssel Team Name FirstDate LastDate Datum Umsatz
000110 Nordwesten Spengler Aaron - 2011-01-20 2009-08-18 100
000110 Nordwesten Spengler Aaron - 2011-01-20 2009-12-25 200
000110 Südwesten Spengler Aaron 2011-01-21   2011-02-03 300
000110 Südwesten Spengler Aaron 2011-01-21   2011-05-05 400
000120 Nordwesten Ballard John   2013-01-05 2011-06-04 500
000120 Südwesten Ballard John 2013-01-06 2013-03-04 2013-01-20 600
000120 Südwesten Ballard John 2013-03-05   2013-03-10 700
000120 Südwesten Ballard John 2013-03-05   2013-03-13 800
000120 Südwesten Ballard John 2013-03-05   2013-09-21 900