Correspondance entre intervalles et chargement itératif

Le préfixe Intervalmatch d'une instruction LOAD ou SELECT permet de lier des valeurs numériques discrètes à un ou plusieurs intervalles numériques. Il s'agit d'une fonction très puissante qui peut s'utiliser, par exemple, dans les environnements de production.

Utilisation du préfixe IntervalMatch()

La correspondance d'intervalle la plus simple est la suivante : vous disposez d'une liste de nombres ou de dates (d'événements) dans une table et d'une liste d'intervalles dans une seconde table. L'objectif est de lier ces deux tables. En général, il s'agit d'une relation de plusieurs à plusieurs. Autrement dit, un intervalle peut comporter plusieurs dates s'y rapportant et une date peut faire partie de plusieurs intervalles. Pour résoudre ce cas, vous devez créer une table de correspondances entre les deux tables initiales. Il existe plusieurs méthodes pour y parvenir.

La méthode la plus simple pour résoudre ce problème dans Qlik Sense est celle qui consiste à placer le préfixe IntervalMatch() devant une instruction LOAD ou SELECT. L'instruction LOAD/SELECT doit seulement contenir deux champs, From et To, qui définissent les intervalles. Le préfixe IntervalMatch() génère ensuite toutes les combinaisons possibles entre les intervalles chargés et un champ numérique précédemment chargé, spécifié sous forme de paramètre du préfixe.

Procédez comme suit :

1. Créez une application et nommez-la.
2. Ajoutez une nouvelle section de script dans l'éditeur de chargement de données.
3. Appelez les sections Events.

4. Sous DataFiles dans le menu droit, cliquez sur Sélectionner des données.

5. Téléchargez, puis sélectionnez Events.txt.
6. Dans la fenêtre Sélectionner des données depuis, cliquez sur Insérer le script.
7. Téléchargez, puis sélectionnez Intervals.txt.
8. Dans la fenêtre Sélectionner des données depuis, cliquez sur Insérer le script.
9. Dans le script, nommez la première table Events et nommez la deuxième table Intervals.
   
10. À la fin du script, ajoutez un préfixe IntervalMatch pour créer une troisième table chargée de relier les deux premières :

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

11. Le script devrait avoir l'aspect suivant :

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. Cliquez sur Charger les données.
13. Ouvrez le Visionneur de modèle de données. Le modèle de données a l'aspect suivant :



Le modèle de données contient une clé composite (les champs IntervalBegin et IntervalEnd), qui se manifestera sous la forme d'une clé synthétique Qlik Sense.

Les tables de base sont les suivantes :

• La table Events qui contient exactement un enregistrement par événement.
• La table Intervals qui contient exactement un enregistrement par intervalle.
• La table de correspondances qui contient exactement un enregistrement par combinaison d'événement et d'intervalle, et qui assure la liaison des deux tables précédentes.

Sachez qu'un événement peut faire partie de plusieurs intervalles si ceux-ci se chevauchent. De même, plusieurs événements peuvent appartenir à un même intervalle.

Il s'agit d'un modèle de données optimal, dans le sens où il est normalisé et compact. Les tables Events et Intervals demeurent toutes deux inchangées et contiennent le nombre d'enregistrements initial. Tous les calculs de Qlik Sense appliqués à ces tables, par exemple, Count(EventID), fonctionneront et aboutiront aux bons résultats.

Utilisation d'une boucle While et du chargement itératif IterNo()

Vous pouvez parvenir pratiquement au même résultat dans la table de correspondance en utilisant une boucle While et IterNo() qui crée des valeurs énumérables entre les limites supérieure et inférieure de l'intervalle.

Il est possible de créer une boucle au sein de l'instruction LOAD au moyen de la clause While. Par exemple :

Une telle instruction LOAD créera une boucle sur chaque enregistrement d'entrée et chargera le résultat en permanence tant que l'expression indiquée dans la clause While est vraie. La fonction IterNo() renvoie 1 dans la première itération, 2 dans la deuxième, etc.

Vous disposez d'un identifiant principal pour les intervalles, intitulé IntervalID. Par conséquent, la seule différence notable dans le script est le mode de création de la table de correspondances :

Procédez comme suit :

1. Remplacez les instructions Bridgetable existantes par le script suivant :

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

2. Cliquez sur Charger les données.
3. Ouvrez le Visionneur de modèle de données. Le modèle de données a l'aspect suivant :



En général, la solution aux trois tables est la meilleure, car elle permet de définir une relation plusieurs à plusieurs entre les intervalles et les événements. Cependant, il arrive souvent que vous sachiez qu'un événement ne peut faire partie que d'un seul intervalle. Dans ce cas, la table de correspondances est vraiment superflue. Vous pouvez stocker directement IntervalID dans la table d'événements. Pour ce faire, il existe plusieurs méthodes, mais la plus pertinente consiste à joindre Bridgetable à la table Events.

4. Ajoutez le script suivant à la fin de votre script :

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

5. Cliquez sur Charger les données.
6. Ouvrez le Visionneur de modèle de données. Le modèle de données a l'aspect suivant :

Intervalles ouverts et fermés

L'ouverture et la fermeture d'un intervalle sont déterminées par l'état des extrémités, c.-à-d. si celles-ci sont incluses ou pas dans l'intervalle.

• Si les extrémités font partie de l'intervalle, il s'agit d'un intervalle fermé :

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

• Si les extrémités ne font pas partie de l'intervalle, il s'agit d'un intervalle ouvert :

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

• Si une seule extrémité fait partie de l'intervalle, il s'agit d'un intervalle semi-ouvert :

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

Si vous rencontrez un cas où les intervalles se chevauchent et qu'un nombre peut faire partie de plus d'un intervalle, il est généralement préconisé d'utiliser des intervalles fermés.

Cependant, dans certains cas, vous ne souhaitez pas d'intervalles qui se chevauchent, mais un nombre faisant partie d'un seul intervalle. Un problème se pose alors si un point correspond à la fois à la fin d'un intervalle et au début du suivant. Un nombre de ce type est attribué aux deux intervalles. Dans ce cas, il est préférable d'utiliser des intervalles semi-ouverts.

Une solution pratique à ce problème consiste à soustraire une très petite quantité de la valeur de fin de tous les intervalles, afin de créer des intervalles fermés qui ne se chevauchent pas. S'il s'agit de dates, le moyen le plus simple consiste à employer la fonction DayEnd(), qui renvoie la dernière milliseconde du jour :

Vous pouvez aussi choisir de soustraire une petite quantité en procédant manuellement. Dans ce cas, veillez à ce que la quantité soustraite ne soit pas trop petite, car l'opération est arrondie à 52 chiffres binaires significatifs (14 chiffres décimaux). Si vous utilisez une quantité trop petite, la différence ne sera pas significative et vous retomberez sur le nombre d'origine.