Dopasowywanie interwałów do danych dyskretnych
Prefiks intervalmatch przed instrukcją LOAD lub SELECT służy do powiązania dyskretnych wartości liczbowych z dowolną liczbą interwałów liczbowych. Jest to funkcja o ogromnych możliwościach, która może być stosowana na przykład w środowisku produkcyjnym.
Przykład użycia prefiksu intervalmatch
Przyjrzyjmy się poniższym dwóm tabelom. Pierwsza tabela przedstawia początek i koniec produkcji różnych zamówień. Druga tabela przedstawia pewne dyskretne wydarzenia. Należy powiązać dyskretne wydarzenia z zamówieniami, aby na przykład ustalić zmianę realizującą każde zamówienie lub stwierdzić, których zamówień dotyczyły zakłócenia.
Start | End | Kolejność |
---|---|---|
01:00 | 03:35 | A |
02:30 | 07:58 | B |
03:04 | 10:27 | C |
07:23 | 11:43 | D |
Time | Event | Komentarz |
---|---|---|
00:00 | 0 | Początek zmiany 1 |
01:18 | 1 | Zatrzymanie linii |
02:23 | 2 | Restart linii na 50% |
04:15 | 3 | Szybkość linii 100% |
08:00 | 4 | Początek zmiany 2 |
11:43 | 5 | Koniec produkcji |
Najpierw należy zatem w zwykły sposób załadować obie tabele, a następnie powiązać pole Time z interwałami wyznaczonymi przez wartości pól Start i End:
SELECT * from OrderLog;
SELECT * from EventLog;
Intervalmatch (Time) SELECT Start,End from OrderLog;
Następnie można już utworzyć tabelę w Qlik Sense w sposób przedstawiony poniżej:
Time | Event | Komentarz | Order | Start | End |
---|---|---|---|---|---|
0:00 | 0 | Początek zmiany 1 | - | - | - |
1:18 | 1 | Zatrzymanie linii | A | 1:00 | 3:35 |
2:23 | 2 | Restart linii na 50% | A | 1:00 | 3:35 |
4:15 | 3 | Szybkość linii 100% | B | 2:30 | 7:58 |
4:15 | 3 | Szybkość linii 100% | C | 3:04 | 10:.... |
8:00 | 4 | Początek zmiany 2 | C | 3:04 | 10:.... |
8:00 | 4 | Początek zmiany 2 | D | 7:23 | 11:.... |
11:43 | 5 | Koniec produkcji | E | 7:23 | 11:..... |
Teraz wyraźnie widać, że zatrzymanie linii miało wpływ głównie na zamówienie A, ale zmniejszona prędkość linii wpłynęła również na zamówienia B i C. Tylko zamówienia C i D były częściowo obsłużone przez Shift 2.
W przypadku korzystania z prefiksu intervalmatch należy pamiętać o następujących kwestiach:
- Pole zawierające dyskretne punkty danych (w powyższym przykładzie jest to pole Time) musi być wczytane do programu Qlik Sense przed wykonaniem instrukcji intervalmatch. Instrukcja intervalmatch nie odczytuje tego pola z tabeli w bazie danych.
- Tabela wczytywana instrukcją intervalmatch LOAD lub SELECT musi zawsze zawierać dokładnie dwa pola (w powyższym przykładzie są to pola Start i End). Aby możliwe było utworzenie powiązania z innymi polami, konieczne jest wczytanie pól interwałów wraz z polami dodatkowymi w ramach osobnej instrukcji LOAD lub SELECT (w powyższym przykładzie jest to pierwsza instrukcja SELECT).
- Interwały są zawsze zamknięte, czyli punkty końcowe są zawarte w interwale. Podanie limitów nieliczbowych powoduje odrzucenie interwału (niezdefiniowany), natomiast limity równe NULL oznaczają nieograniczone rozszerzenie interwału.
- Interwały mogą na siebie zachodzić, a wartości dyskretne zostaną powiązane ze wszystkimi pasującymi interwałami.
Korzystanie z rozszerzonej składni prefiksu intervalmatch w celu rozwiązywania problemów z powoli zmieniającymi się wymiarami
Za pomocą rozszerzonej składni prefiksu intervalmatch można skutecznie rozwiązać dobrze znany problem powoli zmieniających się wymiarów w danych źródłowych.
Przykładowy skrypt:
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;
Instrukcja nullinterpret jest wymagana jedynie w przypadku ładowania danych z pliku tabeli, ponieważ w tym przypadku brakujące wartości są zdefiniowane jako puste ciągi znaków, a nie wartości NULL.
Załadowanie danych z tabeli IntervalTable dałoby w wyniku następującą tabelę:
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 |
Instrukcja nullasvalue pozwala na odwzorowanie wartości NULL na wymienione pola.
Następnie należy utworzyć pola atrybutów Key, FirstDate i LastDate za pomocą instrukcji previous i order by, po czym tabela IntervalTable zostanie zastąpiona tabelą klucza i usunięta.
Załadowanie danych z tabeli Transact dałoby w wyniku następującą tabelę:
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 |
Instrukcja intervalmatch poprzedzona klauzulą inner join zastępuje powyższy klucz kluczem syntetycznym, który tworzy połączenie z tabelą Transact. W efekcie powstaje następująca tabela:
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 |