Dopasowywanie interwałów i ładowanie iteracyjne

Prefiks Intervalmatch przed instrukcją LOAD lub SELECT służy do łączenia 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.

Używanie prefiksu IntervalMatch()

Podstawowy przykład dopasowywania interwałów to sytuacja, w której jedna tabela zawiera listę liczb lub dat (zdarzeń), a druga tabela listę przedziałów. Chodzi o powiązanie tych dwóch tabel. W przypadku ogólnym jest to relacja o krotności wiele do wielu — do interwału może należeć wiele dat, a każda data może należeć do wielu interwałów. Rozwiązanie tego problemu wymaga utworzenia tabeli pomostowej między dwiema tabelami pierwotnymi. Można to zrobić na kilka sposobów.

Najprostszym sposobem na rozwiązanie tego problemu w Qlik Sense jest wykorzystanie prefiksu IntervalMatch() przed każdą instrukcją LOAD lub SELECT. Instrukcja LOAD lub SELECT musi zawierać tylko dwa pola definiujące interwały: pole From i pole To. Prefiks IntervalMatch() posłuży następnie do generowania wszystkich kombinacji załadowanych interwałów i wcześniej załadowanego pola liczbowego wskazanego jako parametr prefiksu.

Wykonaj następujące czynności:

1. Utwórz nową aplikację i nadaj jej nazwę.
2. Dodaj nową sekcję skryptu w edytorze ładowania danych.
3. Wywołaj sekcje Events.

4. W sekcji DataFiles dostępnej po prawej stronie kliknij przycisk Wybierz dane.

5. Prześlij, a następnie wybierz Events.txt.
6. W oknie Wybierz dane z kliknij przycisk Wstaw skrypt.
7. Prześlij, a następnie wybierz Intervals.txt.
8. W oknie Wybierz dane z kliknij przycisk Wstaw skrypt.
9. W skrypcie pierwszej tabeli nadaj nazwę Zdarzenia, a drugiej Intervals.
   
10. Na końcu skryptu dodaj IntervalMatch, aby utworzyć trzecią tabelę łączącą dwie pierwsze tabele:

BridgeTable:
IntervalMatch (EventDate)
LOAD distinct IntervalBegin, IntervalEnd
Resident Intervals;

11. Skrypt powinien wyglądać następująco:

Events:
LOAD
    EventID,
    EventDate,
    EventAttribute
FROM [lib://DataFiles/Events.txt] 
(txt, utf8, embedded labels, delimiter is '\t', msq);
 
Intervals:
LOAD
    IntervalID,
    IntervalAttribute,
    IntervalBegin,
    IntervalEnd
FROM [lib://DataFiles/Intervals.txt] 
(txt, utf8, embedded labels, delimiter is '\t', msq);
 
BridgeTable:
IntervalMatch (EventDate)
LOAD distinct IntervalBegin, IntervalEnd
Resident Intervals;

12. Kliknij polecenie Ładuj dane.
13. Otwórz przeglądarkę modelu danych. Model danych wygląda następująco:



Model danych zawiera klucz złożony (pola IntervalBegin i IntervalEnd), który będzie wyświetlany w Qlik Sense jako klucz syntetyczny.

Podstawowe tabele to:

• tabela Events zawierająca dokładnie jeden rekord na zdarzenie;
• tabela Intervals zawierająca dokładnie jeden wiersz na interwał;
• tabela pomostowa zawierająca dokładnie jeden wiersz dla każdej kombinacji zdarzenia i interwału, łącząca dwie poprzednie tabele.

Należy pamiętać, że w przypadku zachodzących na siebie interwałów jedno zdarzenie może należeć do kilku interwałów, a do jednego interwału może oczywiście należeć wiele zdarzeń.

Jest to optymalny model danych: znormalizowany i zwarty. Tabele Events i Intervals pozostają bez zmian i każda zawiera oryginalną liczbę wierszy. Wszystkie obliczenia programu Qlik Sense wykonywane na tych tabelach, na przykład Count(EventID), będą wykonywane prawidłowo i z prawidłowymi wynikami.

Używanie pętli While i funkcji IterNo() do ładowania iteracyjnego

Bardzo podobną tabelę pomostową można utworzyć z użyciem pętli While i funkcji IterNo(), która generuje wartości nadające się do iterowania między dolnym a górnym limitem interwału.

Pętlę w instrukcji LOAD można utworzyć za pomocą klauzuli While. Na przykład:

Tak sformułowana instrukcja LOAD będzie w pętli odczytywać i ładować każdy kolejny wiersz wejściowy, dopóki wyrażenie podane w klauzuli While będzie mieć wartość true. Funkcja IterNo() zwraca wartość „1” przy pierwszej iteracji, „2” w drugiej i tak dalej.

W tabeli interwałów jest klucz główny (IntervalID), więc jedyną różnicą w skrypcie będzie sposób tworzenia tabeli pomostowej:

Wykonaj następujące czynności:

1. Zastąp istniejące instrukcje Bridgetable następującym skryptem:

BridgeTable:
LOAD distinct * Where Exists(EventDate);
LOAD IntervalBegin + IterNo() - 1 as EventDate, IntervalID
    Resident Intervals
    While IntervalBegin + IterNo() - 1 <= IntervalEnd;

2. Kliknij polecenie Ładuj dane.
3. Otwórz przeglądarkę modelu danych. Model danych wygląda następująco:



Ogólnie rozwiązanie z trzema tabelami jest najlepsze, ponieważ pozwala na stosowanie relacji „wiele do wielu” między interwałami i zdarzeniami. Bardzo często wiadomo jednak z góry, że każde zdarzenie może przynależeć tylko do jednego interwału. W tym przypadku tabela pomostowa nie jest konieczna. Wartości kolumny IntervalID można zapisać bezpośrednio w tabeli zdarzeń. Można to zrobić na kilka sposobów, ale najwygodniejsze będzie sprzężenie tabeli Bridgetable z tabelą Events.

4. Dodaj następujący skrypt na końcu swojego skryptu:

Join (Events)
LOAD EventDate, IntervalID
Resident BridgeTable;
 
Drop Table BridgeTable;

5. Kliknij polecenie Ładuj dane.
6. Otwórz przeglądarkę modelu danych. Model danych wygląda następująco:

Interwały otwarte i zamknięte

Interwał jest otwarty lub zamknięty w zależności od tego, czy obejmuje punkty końcowe.

• Jeśli interwał obejmuje punkty końcowe, jest to interwał zamknięty:

[a,b] = {x ∈ ℝ ∣ a ≤ x ≤ b}

• Jeśli interwał nie obejmuje punktów końcowych, jest to interwał otwarty:

]a,b[ = {x ∈ ℝ ∣ a < x < b}

• Jeśli interwał obejmuje tylko jeden punkt końcowy, jest to interwał półotwarty:

[a,b[ = {x ∈ ℝ ∣ a ≤ x < b}

Jeśli interwały zachodzą na siebie i niektóre liczby mogą należeć do więcej niż jednego interwału, zwykle wskazane jest użycie interwałów zamkniętych.

Czasami jednak interwały nie zachodzą na siebie i każda liczba ma należeć do dokładnie jednego interwału. Pojawia się zatem problem, jeśli jeden punkt jest końcem jednego interwału i początkiem następnego. Liczba o wartości odpowiadającej temu punktowi zostanie przypisana do obu interwałów. Odpowiednim rozwiązaniem będą więc interwały półotwarte.

Praktycznym rozwiązaniem tego problemu jest odjęcie bardzo małej liczby od wartości końcowej każdego interwału, aby uzyskać w ten sposób interwały zamknięte, ale niezachodzące na siebie. Jeśli używane liczby są datami, najprościej w tym celu skorzystać z funkcji DayEnd(), która zwraca ostatnią milisekundę doby:

Niewielką liczbę można również odjąć ręcznie. W takim przypadku trzeba uważać, by odejmowana liczba nie była za mała, gdyż wynik operacji zostanie zaokrąglony do 52 znaczących cyfr binarnych (14 cyfr dziesiętnych). Odjęcie zbyt małej liczby nie zmieni wartości w sposób znaczący, więc wynik będzie znów liczbą pierwotną.