Correspondance entre intervalles et données discrètes

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, comme l'illustre l'exemple ci-dessous.

Exemple d'Intervalmatch

Considérez les deux tables ci-dessous. La première table affiche le début et la fin de la production de différentes commandes. La seconde table affiche des événements discrets. Comment est-il possible d'associer les événements discrets aux commandes, de manière à savoir, par exemple, quelles commandes ont subi des perturbations et quelles commandes ont été traitées par telle ou telle équipe ?

Table OrderLog
Début End Ordre
01:00 03:35 A
02:30 07:58 B
03:04 10:27 C
07:23 11:43 D
Table EventLog
Time Event Commentaire
00:00 0 Début de l'équipe 1
01:18 1 Arrêt de la ligne
02:23 2 Redémarrage de la ligne 50 %
04:15 3 Vitesse de la ligne 100 %
08:00 4 Début de l'équipe 2
11:43 5 Fin de la production

Chargez tout d'abord les deux tables selon la procédure habituelle, puis liez le champ Time aux intervalles définis par les champs Start et End :

SELECT * from OrderLog;

SELECT * from EventLog;

Intervalmatch (Time) SELECT Start,End from OrderLog;

Vous pouvez à présent créer une table dans Qlik Sense, comme celle qui suit :

Table dont le champ Time est lié aux intervalles définis par Start et End
Time Event Commentaire Ordre Début End
0:00 0 Début de l'équipe 1 - - -
1:18 1 Arrêt de la ligne A 1:00 3:35
2:23 2 Redémarrage de la ligne 50 % A 1:00 3:35
4:15 3 Vitesse de la ligne 100 % B 2:30 7:58
4:15 3 Vitesse de la ligne 100 % C 3:04 10:....
8:00 4 Début de l'équipe 2 C 3:04 10:....
8:00 4 Début de l'équipe 2 D 7:23 11:....
11:43 5 Fin de la production E 7:23 11:.....

Nous voyons à présent facilement que la commande A a principalement été affectée par l'arrêt de la ligne, mais que la vitesse réduite de la ligne a également affecté les commandes B et C. Seules les commandes C et D ont été partiellement traitées par l'équipe Shift 2.

Prenez note des points suivants concernant l'utilisation de intervalmatch :

  • Avant l'instruction intervalmatch, le champ contenant les points de données discrets (Time dans l'exemple ci-dessus) doit déjà avoir été lu dans Qlik Sense. L'instruction intervalmatch ne lit pas ce champ à partir de la table de la base de données.
  • La table lue dans l'instruction intervalmatch LOAD ou SELECT doit toujours contenir exactement deux champs (Start et End dans l'exemple ci-dessus). Afin d'établir un lien avec d'autres champs, vous devez lire les champs d'intervalle en même temps que les champs supplémentaires dans une instruction LOAD ou SELECT distincte (la première instruction SELECT dans l'exemple ci-dessus).
  • Les intervalles sont toujours fermés. Autrement dit, les points de fin sont inclus dans l'intervalle. Avec des limites non numériques, l'intervalle est ignoré (indéfini) tandis qu'avec des limites NULL, il est étendu de manière indéfinie (il devient illimité).
  • Les intervalles peuvent se superposer et les valeurs discrètes sont alors liées à tous les intervalles correspondants.

Résolution des problèmes de dimensions changeant progressivement à l'aide de la syntaxe intervalmatch étendue

La syntaxe intervalmatch étendue peut servir à traiter le problème bien connu des dimensions changeant progressivement dans les données source.

Échantillon de script :

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;

L'instruction nullinterpret n'est requise que lorsque le programme lit des données à partir d'un fichier de table, puisque les valeurs manquantes sont définies comme des chaînes vides plutôt que comme des valeurs NULL.

Le chargement des données à partir de IntervalTable aboutirait à la table suivante :

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

L'instruction nullasvalue permet de mapper des valeurs NULL aux champs répertoriés.

Créez les champs d'attribut Key, FirstDate et LastDate à l'aide de previous et order by ; la table IntervalTable est alors abandonnée et remplacée par cette table de clés.

Le chargement des données à partir de Transact aboutirait à la table suivante :

Table dont les données sont chargées à partir de Transact
Key Nom 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

L'instruction intervalmatch précédée de inner join remplace la clé ci-dessus par une clé synthétique qui établit une connexion avec la table Transact, aboutissant ainsi à la table suivante :

Table dont l'instruction intervalmatch est précédée par inner join
Key Équipe Nom 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