Сопоставление интервалов с дискретными данными
Префикс intervalmatch для операторов LOAD или SELECT используется для связывания дискретных числовых значений с одним или несколькими числовыми интервалами. Это очень полезная функция, которая может использоваться, например, в производственных средах.
Пример применения Intervalmatch
Взгляните на две расположенные ниже таблицы. В первой таблице задано время начала и конца выполнения различных заказов. Во второй таблице заданы некоторые отдельные события. Как связать отдельные события с заказами, чтобы знать, например, какие заказы были затронуты нарушениями, и в какие смены были выполнены определенные заказы?
Start | End | Order |
---|---|---|
01:00 | 03:35 | A |
02:30 | 07:58 | B |
03:04 | 10:27 | C |
07:23 | 11:43 | D |
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 | 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 приводит к созданию следующей таблицы:
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 приводит к созданию следующей таблицы:
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, что позволяет создать следующую таблицу:
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 |