Matcha intervaller och iterativ inläsning

Prefixet Intervalmatch framför en LOAD- eller SELECT-sats används för att länka diskreta numeriska värden till ett eller flera numeriska intervall. Detta är en mycket kraftfull funktion, som kan användas i exempelvis produktionsmiljö.

Använda prefixet IntervalMatch()

Den mest grundläggande intervallmatchningen är när du har en lista med tal eller datum (händelser) i en tabell och en lista över intervall i en andra tabell. Målet är att länka de två tabellerna. Oftast är detta ett många-till-många-förhållande, det vill säga att ett intervall kan ha många datum som hör till det och ett datum kan höra till många intervall. För att lösa detta måste du skapa en kopplingstabell mellan de två ursprungliga tabellerna. Detta kan du göra på flera sätt.

Det enklaste sättet att lösa det här problemet i Qlik Sense är att använda prefixet IntervalMatch() framför antingen en LOAD- eller en SELECT-sats. LOAD/SELECT-satsen får bara innehålla två fält, nämligen fälten From och To som anger intervallen. Prefixet IntervalMatch() genererar då alla kombinationer mellan de inlästa intervallen och ett tidigare inläst numeriskt fält, som angetts som parameter för prefixet.

Gör följande:

1. Skapa en ny app och ge den ett namn.
2. Lägg till ett nytt skriptavsnitt i Skriptredigeraren.
3. Kalla avsnitten Events.

4. Klicka på Välj data under DataFiles i menyn till höger.

5. Ladda upp och välj sedan Events.txt.
6. Klicka på Infoga skript i fönstret Välj data från.
7. Ladda upp och välj sedan Intervals.txt.
8. Klicka på Infoga skript i fönstret Välj data från.
9. I skriptet ger du den första tabellen namnet Events, och den andra tabellen namnet Intervals.
   
10. Lägg till en IntervalMatch-sats i slutet av skriptet för att skapa en tredje tabell som kopplar samman de två första tabellerna:

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

11. Ditt skript bör se ut så här:

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. Klicka på Ladda data.
13. Öppna datamodellvyn. Datamodellen ser ut så här:



Datamodellen innehåller en sammansatt nyckel (fälten IntervalBegin och IntervalEnd) som tar formen av en syntetisk Qlik Sense-nyckel.

De grundläggande tabellerna är:

• Events-tabellen som innehåller exakt en post per händelse.
• Intervals-tabellen som innehåller exakt en post per intervall.
• Kopplingstabellen som innehåller exakt en post per kombination av händelse och intervall, och som länkar de två andra tabellerna.

Observera att en händelse kan höra till flera intervaller om intervallerna överlappar varandra. Ett intervall kan såklart ha flera händelser som hör till det.

Den här datamodellen är optimal i den mening att den är normaliserad och kompakt. Events-tabellen och Intervals-tabellen är båda oförändrade och innehåller det ursprungliga antalet poster. Alla Qlik Sense-beräkningar som används med de här tabellerna, till exempel Count(EventID), kommer att fungera och utvärderas korrekt.

Använda en While-slinga och iterativ inläsning IterNo()

Du kan åstadkomma nästan samma kopplingstabell med hjälp av en While-slinga och IterNo() som skapar uppräkningsbara värden mellan det lägre och det övre gränsvärdet för intervallet.

En slinga i LOAD-satsen kan skapas med hjälp av While-satsen. Exempel:

En sådan LOAD-sats skapar en slinga för varje indatapost och läser in denna om och om igen så länge uttrycket i While-satsen är sant. IterNo()-funktionen returnerar ”1” i den första iterationen, ”2” i den andra och så vidare.

Du har en primärnyckel för intervallen, IntervalID, så den enda skillnaden i skriptet är hur kopplingstabellen skapas:

Gör följande:

1. Ersätt de befintliga Bridgetable-satserna med följande skript:

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

2. Klicka på Ladda data.
3. Öppna datamodellvyn. Datamodellen ser ut så här:



Normalt är lösningen med tre tabeller bäst, eftersom du då kan ha ett många-till-många-förhållande mellan intervall och händelser. En vanlig situation är dock att du vet att en händelse bara kan höra till ett enda intervall. I sådana fall är kopplingstabellen inte nödvändig. IntervalID kan lagras direkt i händelsetabellen. Det finns flera sätt att åstadkomma detta, men det mest användbara är att koppla Bridgetable till Events-tabellen.

4. Lägg till följande kod i slutet av skriptet:

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

5. Klicka på Ladda data.
6. Öppna datamodellvyn. Datamodellen ser ut så här:

Öppna och stängda intervaller

Om en tabell är öppen eller stängd avgörs av slutpunkterna, beroende på om dessa är inkluderade i intervallet eller inte.

• Om slutpunkterna ingår är det ett stängt intervall:

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

• Om slutpunkterna inte ingår är det ett öppet intervall:

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

• Om en slutpunkt ingår är det ett halvöppet intervall:

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

I ditt fall, där intervallen kan överlappa varandra och ett tal kan höra till fler än ett intervall, behöver du vanligtvis använda stängda intervall.

I vissa fall vill du kanske dock inte ha överlappande intervall, utan att ett tal bara ska höra till ett intervall. I sådana fall kommer det att orsaka problem om en punkt är slutet på ett intervall men på samma gång början på nästa. Ett tal med det värdet kommer att ingå i båda intervallen. I den här situationen bör du använda halvöppna intervall.

En praktisk lösning på det här problemet är att subtrahera ett mycket litet tal från slutvärdet på varje intervall, så att du får intervall som är slutna men som inte överlappar varandra. Om talen är datum blir det enklast att göra detta genom att använda DayEnd()-funktionen som returnerar den sista millisekunden av dagen:

Du kan även subtrahera ett litet tal manuellt. Om du gör det måste du se till att talet du subtraherar inte är för litet, eftersom operationen avrundas till 52 signifikanta binära siffror (14 decimalsiffor). Om talet du subtraherar är för litet blir skillnaden inte signifikant och du kommer fortfarande att använda det ursprungliga talet.