Сопоставление интервалов с дискретными данными

Префикс intervalmatch для операторов LOAD или SELECT используется для связывания дискретных числовых значений с одним или несколькими числовыми интервалами. Это очень полезная функция, которая может использоваться, например, в производственных средах, как показано в примере ниже.

Пример применения Intervalmatch

Взгляните на две расположенные ниже таблицы. В первой таблице задано время начала и конца выполнения различных заказов. Во второй таблице заданы некоторые отдельные события. Как связать отдельные события с заказами, чтобы знать, например, какие заказы были затронуты нарушениями, и в какие смены были выполнены определенные заказы?

Table OrderLog
Start End Order
01:00 03:35 A
02:30 07:58 B
03:04 10:27 C
07:23 11:43 D
Table EventLog
Time Event Comment
00:00 0 Start of shift 1
01:18 1 Line stop
02:23 2 Line restart 50%
04:15 3 Line speed 100%
08:00 4 Start of shift 2
11:43 5 End of production

Сначала загрузите две таблицы как обычно, затем свяжите поле Time с интервалами, определенными полями Start и End:

SELECT * from OrderLog;

SELECT * from EventLog;

Intervalmatch (Time) SELECT Start,End from OrderLog;

Теперь в программе Qlik Sense можно создать таблицу следующим образом:

Таблица с полем Time, связанным с интервалами, определенными значениями полей Start и End
Time Event Comment Order Start End
0:00 0 Start of shift 1 - - -
1:18 1 Line stop A 1:00 3:35
2:23 2 Line restart 50% A 1:00 3:35
4:15 3 Line speed 100% B 2:30 7:58
4:15 3 Line speed 100% C 3:04 10:....
8:00 4 Start of shift 2 C 3:04 10:....
8:00 4 Start of shift 2 D 7:23 11:....
11:43 5 End of production E 7:23 11:.....

Отсюда видно, что в основном остановка линии повлияла на заказ A, а снижение скорости линии повлияло также на заказы B и C. Только заказы C и D были частично обработаны сменой Shift 2.

При использовании оператора intervalmatch обратите внимание на следующие моменты:

  • До оператора intervalmatch поле, которое содержит дискретные точки диаграммы (Time в приведенных выше примерах), уже должно быть считано в программу Qlik Sense. Оператор intervalmatch сам не считывает это поле из таблицы базы данных!
  • Таблица, считанная с помощью оператора intervalmatch LOAD или SELECT, должна содержать ровно два поля (Start и End в примере, приведенном выше). Для установки связи с другими полями необходимо выполнить считывание из полей интервала, а также других дополнительных полей с помощью отдельного оператора LOAD или SELECT (первый оператор SELECT в указанном выше примере).
  • Интервалы всегда закрыты. Это означает, что конечные точки включены в интервал. Нечисловые пределы выводят игнорируемый интервал (неопределенный), а пределы со значением NULL расширяют интервалы до неопределенных значений (неограниченные интервалы).
  • Интервалы могут накладываться друг на друга, а дискретные значения будут связаны со всеми соответствующими интервалами.

Использование расширенного синтаксиса intervalmatch для разрешения неполадок с медленно изменяющимися измерениями

Расширенный синтаксис intervalmatch может использоваться для разрешения известных неполадок с медленно изменяющимися измерениями в исходных данных.

Пример скрипта:

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;

Оператор nullinterpret необходим только при считывании данных из табличного файла, поскольку пропущенные значения определяются как пустые строки, а не как значения NULL.

Загрузка данных из оператора IntervalTable приводит к созданию следующей таблицы:

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

Оператор nullasvalue разрешает сопоставление значений NULL с полями в списке.

Создайте Key, FirstDate, LastDate (поля атрибутов) с помощью элементов previous и order by, после чего параметр IntervalTable исключается и заменяется этой таблицей ключей.

Загрузка данных из оператора Transact приводит к созданию следующей таблицы:

Таблица с данными, загруженными из Transact
Key Name Date Sales
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

Последовательно заданные операторы intervalmatch и inner join заменяют вышеуказанный ключ синтетическим, подсоединяемым к таблице оператором Transact, что позволяет создать следующую таблицу:

Таблица с оператором intervalmatch, которому предшествует inner join
Key Team Name FirstDate LastDate Date Sales
000110 Northwest Spengler Aaron - 2011-01-20 2009-08-18 100
000110 Northwest Spengler Aaron - 2011-01-20 2009-12-25 200
000110 Southwest Spengler Aaron 2011-01-21   2011-02-03 300
000110 Southwest Spengler Aaron 2011-01-21   2011-05-05 400
000120 Northwest Ballard John   2013-01-05 2011-06-04 500
000120 Southwest Ballard John 2013-01-06 2013-03-04 2013-01-20 600
000120 Southwest Ballard John 2013-03-05   2013-03-10 700
000120 Southwest Ballard John 2013-03-05   2013-03-13 800
000120 Southwest Ballard John 2013-03-05   2013-09-21 900