Afstemmingsintervallen en iteratief laden

Het prefix Intervalmatch bij een opdracht LOAD of SELECT wordt gebruikt om discrete numerieke waarden aan een of meer numerieke intervallen te koppelen. Dit is een zeer krachtige functie die u bijvoorbeeld in productieomgevingen kunt gebruiken.

Het prefix IntervalMatch() gebruiken

De meest elementaire intervalafstemming is wanneer u een lijst met getallen of datums (gebeurtenissen) hebt in de ene tabel en een lijst met intervallen in een tweede. Het doel is om de twee tabellen te koppelen. Gewoonlijk is dit een veel-op-veel-relatie, oftewel één interval kan vele bijbehorende datums hebben en een datum kan tot vele intervallen behoren. Om dit op te lossen, moet u een overbruggingstabel maken tussen de twee oorspronkelijke tabellen. Er zijn verschillende manieren om dit te doen.

De eenvoudigste manier om dit probleem op te lossen in Qlik Sense is om het prefix IntervalMatch() vóór een opdracht LOAD of SELECT te plaatsen. De LOAD-/SELECT-opdracht hoeft slechts twee velden te bevatten, namelijk de velden “From” en “To” voor het definiëren van de intervallen. Het prefix IntervalMatch() genereert vervolgens alle combinaties tussen de geladen intervallen en een eerder geladen numeriek veld, dat is opgegeven als parameter voor het prefix.

Doe het volgende:

1. Maak een nieuwe app en geef deze een naam.
2. Voeg een nieuwe scriptsectie toe in de Editor voor het laden van gegevens.
3. Roep de secties Events aan.

4. Onder DataFiles in het rechtermenu klikt u op Gegevens selecteren.

5. Upload en selecteer Events.txt.
6. In het venster Selecteer gegevens uit klikt u op Script invoeren.
7. Upload en selecteer Intervals.txt.
8. In het venster Selecteer gegevens uit klikt u op Script invoeren.
9. Noem in het script de eerste tabel Events en de tweede tabel Intervals.
   
10. Voeg aan het einde van het script een IntervalMatch toe om een derde tabel te maken en de eerste twee tabellen te overbruggen:

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

11. Uw script zou er als volgt moeten uitzien:

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. Klik op Gegevens laden.
13. Open de gegevensmodelviewer. Het gegevensmodel ziet er als volgt uit:



Het gegevensmodel bevat een samengestelde sleutel (de velden IntervalBegin en IntervalEnd) die zichzelf manifesteert als een synthetische sleutel in Qlik Sense.

De basistabellen zijn:

• De tabel Events die precies één record per gebeurtenis bevat.
• De tabel Intervals die exact één record per interval bevat.
• De overbruggingstabel die exact één record per combinatie van gebeurtenis en interval bevat en die de twee eerdere tabellen aan elkaar koppelt.

Een gebeurtenis kan overigens bij verschillende intervallen behoren als de intervallen elkaar overlappen. En een interval kan natuurlijk verschillende gebeurtenissen hebben die hier deel van uitmaken.

Dit gegevensmodel is optimaal in die zien dat het genormaliseerd en compact is. De tabel Events en de tabel Intervals zijn beide ongewijzigd en bevatten het oorspronkelijke aantal records. Alle berekeningen in Qlik Sense die worden uitgevoerd op deze tabellen, zoals Count(EventID), werken en worden op correcte wijze geëvalueerd.

Een While-lus en iteratief laden van IterNo() gebruiken

U kunt bijna dezelfde overbruggingstabel verkrijgen via een While-lus en IterNo(), waarmee telbare waarden worden gemaakt tussen de boven- en ondergrenzen van het interval.

Een lus binnen in de opdracht LOAD kan worden gemaakt met behulp van de clausule While. Bijvoorbeeld:

Een dergelijke LOAD-opdracht voert een lusbewerking uit op elke invoerrecord en laadt deze steeds opnieuw zolang de uitdrukking in de While-clausule waar is. De functie IterNo() geeft “1” retour bij de eerste iteratie, “2” bij de tweede enzovoort.

U hebt een primaire sleutel voor de intervallen, de IntervalID, dus het enige verschil in het script schuilt in de manier waarop de overbruggingstabel wordt gemaakt:

Doe het volgende:

1. Vervang de bestaande opdrachten Bridgetable door het volgende script:

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

2. Klik op Gegevens laden.
3. Open de gegevensmodelviewer. Het gegevensmodel ziet er als volgt uit:



Over het algemeen is de oplossing met drie tabellen de beste, omdat het veel-op-veel-relaties mogelijk maakt tussen intervallen en gebeurtenissen. Maar een veelvoorkomende situatie is dat u weet dat een gebeurtenis slechts tot een enkel interval kan behoren. In dergelijke gevallen is een overbruggingstabel echt niet nodig. De IntervalID kan rechtstreeks in de tabel van de gebeurtenis worden opgeslagen. Er zijn verschillende manieren om dit voor elkaar te krijgen, maar de meest nuttige is samenvoeging van Bridgetable met de tabel Events.

4. Voeg het volgende toe aan het eind van uw script:

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

5. Klik op Gegevens laden.
6. Open de gegevensmodelviewer. Het gegevensmodel ziet er als volgt uit:

Open en gesloten intervallen

Of een interval open of gesloten is wordt bepaald door de eindpunten, afhankelijk van of deze al dan niet in het interval zijn opgenomen.

• Als de eindpunten zijn opgenomen, is het een gesloten interval:

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

• Als de eindpunten niet zijn opgenomen, is het een open interval:

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

• Als één eindpunt is opgenomen, is het een halfopen interval:

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

Als u een situatie hebt waarin de intervallen elkaar overlappen en een getal bij meer dan één interval kan behoren, moet u gewoonlijk gesloten intervallen gebruiken.

In sommige situaties wilt u echter geen overlappende intervallen en wilt u dat een getal bij slechts één interval behoort. U komt dit in de problemen als één punt het einde van het ene interval en tegelijkertijd het begin van het volgende vormt. Een getal met deze waarde wordt aan beide intervallen toegewezen. Daarom wilt u halfopen intervallen.

Een praktische oplossing voor dit probleem is het aftrekken van een zeer kleine hoeveelheid van de eindwaarde van alle intervallen, waardoor gesloten, maar niet-overlappende intervallen ontstaan. Als uw getallen datums zijn, kunt u dit het gemakkelijkst doen door de functie DayEnd() te gebruiken, die de laatste milliseconde van de dag retourneert:

U kunt ook handmatig een kleine hoeveelheid aftrekken. Als u dat doet, moet u ervoor zorgen dat de afgetrokken hoeveelheid niet te klein is omdat de bewerking wordt afgerond op 52 significante binaire cijfers (14 decimale cijfers). Als u een te kleine hoeveelheid gebruikt, is het verschil niet significant en krijgt u weer het oorspronkelijke getal.