QlikWorld 2020 Global Conference. Weź udział i dowiedz się, jak w pełni wykorzystać dane. Nie zwlekaj. Zarejestruj się już teraz, by zaoszczędzić.

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, co zilustrowano poniżej.

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.

Tabela OrderLog
Start End Kolejność
01:00 03:35 A
02:30 07:58 B
03:04 10:27 C
07:23 11:43 D
Tabela EventLog
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:

Tabela z polem Time połączonym do przedziałów zdefiniowanych przez Start i End
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ę:

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

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ę:

Tabela z danymi załadowanymi z tabeli 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

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:

Tabela z instrukcją intervalmatch poprzedzoną instrukcją 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