Utilisation des fonctions d'inter-enregistrements Peek, Previous et Exists

Ces fonctions sont utilisées lorsque l'évaluation de l'enregistrement actif nécessite une valeur provenant d'enregistrements de données déjà chargés.

Dans cette partie du didacticiel, nous allons examiner les fonctions Peek(), Previous() et Exists().

Peek()

Peek() renvoie la valeur d'un champ dans une table pour une ligne qui a déjà été chargée. Il est possible de spécifier le numéro de ligne et la table. Si aucune ligne n'est spécifiée, le dernier enregistrement précédemment chargé sera utilisé.

 

La valeur de la ligne doit être un entier. 0 renvoie au premier enregistrement, 1 au deuxième et ainsi de suite. Les nombres négatifs indiquent l'ordre des enregistrements à partir de la fin de la table. -1 renvoie ainsi au dernier enregistrement lu.

Si aucune ligne n'est spécifiée, la fonction utilise -1.

Tablename est une étiquette de table sans les deux-points finaux. Si aucun argument tablename n'est spécifié, la table active est utilisée. En cas d'utilisation en dehors de l'instruction LOAD ou pour faire référence à une autre table, l'argument tablename doit être inclus.

Previous()

Previous() recherche la valeur de l'expression expr en utilisant les données de l'enregistrement d'entrée précédent qui n'a pas été ignoré du fait d'une clause where. Dans le premier enregistrement d'une table interne, la fonction renvoie NULL.

 

Il est possible d'imbriquer la fonction Previous() afin d'accéder à des enregistrements encore antérieurs. La fonction recherche les données directement dans la source d'entrée, ce qui vous permet de faire aussi référence à des champs qui n'ont pas été chargés dans Qlik Sense, c'est-à-dire même s'ils n'ont pas été stockés dans la base de données associée.

Exists()

Exists() détermine si une valeur de champ donnée a déjà été chargée dans le champ du script de chargement de données. La fonction renvoie TRUE ou FALSE. Elle peut donc être utilisée dans la clause where d'une instruction LOAD ou d'une instruction IF.

 

Le champ doit exister dans les données chargées jusqu'ici par le script. Expression est une expression qui calcule la valeur de champ à rechercher dans le champ spécifié. Si elle est omise, c'est la valeur de l'enregistrement actif dans le champ spécifié qui est utilisée.

Utilisation de Peek() et Previous()

Dans leur forme la plus simple, Peek() et Previous() permettent d'identifier des valeurs précises dans une table. Voici un échantillon de données dans la table Employees que vous chargerez dans cet exercice.

Pour le moment, cette table collecte seulement les données relatives aux mois, aux embauches et aux cessations d'emploi. Nous allons donc ajouter des champs pour le nombre d'employés (Employee Count) et la variance d'employés (Employee Var) en utilisant les fonctions Peek() et Previous(), qui nous permettront de voir la différence d'effectifs selon les mois.

Procédez comme suit :

1. Ouvrez l'application Advanced Scripting Tutorial.
2. Ajoutez une nouvelle section de script dans l'éditeur de chargement de données.
3. Appelez la section Employees.

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

5. Téléchargez, puis sélectionnez Employees.xlsx.
Note InformationsSous Field names, assurez-vous que l'option Embedded field names est sélectionnée afin d'inclure des champs de table lors du chargement des données.
6. Dans la fenêtre Sélectionner des données depuis, cliquez sur Insérer le script.

Le script devrait avoir l'aspect suivant :

LOAD "Date", Hired, Terminated FROM [lib://DataFiles/Employees.xlsx] (ooxml, embedded labels, table is Sheet1);

7. Modifiez le script comme suit :

   [Employees Init]:
   LOAD
       rowno() as Row,
       Date(Date) as Date,
       Hired,
       Terminated,
       If(rowno()=1, Hired-Terminated,  peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]
   FROM [lib://DataFiles/Employees.xlsx]		
   (ooxml, embedded labels, table is Sheet1);

Les dates indiquées du champ Date de la feuille Excel suivent le format MM/JJ/AAAA. Pour vous assurer que les dates sont interprétées correctement via le format provenant des variables système, la fonction Date est appliquée au champ Date.

La fonction Peek() vous permet d'identifier n'importe quelle valeur chargée pour un champ défini. Dans l'expression, nous commençons par examiner le script pour voir si le paramètre rowno() est égal à 1. S'il est égal à 1, aucun champ Employee Count ne sera présent. Nous indiquons alors dans le champ la différence des embauches et des cessations d'emploi, soit Hired moins Terminated.

Si le paramètre rowno() est supérieur à 1, nous considérons la colonne Employee Count du mois précédent et utilisons sa valeur pour l'ajouter à la différence de la colonne Hired de ce mois moins la colonne Terminated.

Vous observerez également que nous utilisons (-1) dans la fonction Peek(). Cela permet à Qlik Sense de considérer l'enregistrement situé au-dessus de l'enregistrement actif. Si (-1) n'est pas spécifié, Qlik Sense suppose que vous souhaitez examiner l'enregistrement précédent.

8. Ajoutez ce qui suit à la fin du script :

[Employee Count]:
LOAD
	Row,
	Date,
	Hired,
	Terminated,
	[Employee Count],
	If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;
Drop Table [Employees Init];

La fonction Previous() vous permet d'identifier la dernière valeur chargée pour un champ défini. Dans cette expression, nous commençons par examiner le script pour voir si le paramètre rowno() est égal à 1. S'il est égal à 1, nous savons qu'il n'y aura pas de paramètre Employee Var, car il n'existe aucun enregistrement pour le nombre d'employés Employee Count du mois dernier. Nous entrons donc 0 pour la valeur.

Si le paramètre rowno() est supérieur à 1, nous savons qu'un paramètre Employee Var sera présent. Dans ce cas, nous devrons examiner la colonne Employee Count du mois dernier et soustraire cette valeur de la colonne Employee Count du mois actuel afin d'obtenir la valeur du champ Employee Var.

Le script devrait avoir l'aspect suivant :

[Employees Init]:
LOAD
    rowno() as Row,
    Date(Date) as Date,
    Hired,
    Terminated,
    If(rowno()=1, Hired-Terminated,  peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]
FROM [lib://DataFiles/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Employee Count]:
LOAD
    Row,
    Date,
    Hired,
    Terminated,
    [Employee Count],
    If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;	
Drop Table [Employees Init];

9. Cliquez sur Charger les données.

Dans une nouvelle feuille de l'aperçu de l'application, créez une table en utilisant Date, Hired, Terminated, Employee Count et Employee Var comme colonnes de la table. La table résultante doit avoir l'aspect suivant :

Peek() et Previous() vous permettent de cibler des lignes définies dans une table. La principale différence entre les deux réside dans le fait que la fonction Peek() permet à l'utilisateur d'examiner un champ qui n'était pas déjà chargé dans le script tandis que la fonction Previous() peut uniquement considérer un champ déjà chargé. La fonction Previous() fonctionne au niveau de l'entrée de l'instruction LOAD tandis que la fonction Peek() fonctionne au niveau de la sortie de l'instruction LOAD. (La différence entre ces fonctions rappelle celle des fonctions RecNo() et RowNo()). Par conséquent, les deux fonctions se comportent différemment en présence d'une clause Where.

Ainsi, il est préférable d'utiliser la fonction Previous() dans le cas où vous devriez afficher la valeur actuelle par rapport à la valeur précédente. Dans notre exemple, nous avons calculé la variance des employés d'un mois sur l'autre.

L'utilisation de la fonction Peek() est préconisée dans les cas où vous ciblez un champ qui n'a pas encore été chargé dans la table ou lorsque vous avez besoin de cibler une ligne précise. Ce cas est illustré dans l'exemple où nous avons calculé la valeur de la colonne Employee Count en examinant la colonne Employee Count du mois précédent, puis en lui ajoutant la différence entre le nombre des embauches et le nombre des cessations d'emploi du mois actuel. Rappelez-vous que la colonne Employee Count n'était pas un champ dans le fichier d'origine.

Utilisation de Exists()

La fonction Exists() s'utilise souvent avec la clause Where dans le script afin de permettre de charger des données si des données connexes ont déjà été chargées dans le modèle de données.

Dans l'exemple suivant, nous utilisons aussi la fonction Dual() pour attribuer des valeurs numériques à des chaînes.

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 la section People.
4. Saisissez les lignes de script suivantes :

//Add dummy people data
PeopleTemp:
LOAD * INLINE [
PersonID, Person
1, Jane
2, Joe
3, Shawn
4, Sue
5, Frank
6, Mike
7, Gloria
8, Mary
9, Steven,
10, Bill
];
 
//Add dummy age data
AgeTemp:
LOAD * INLINE [
PersonID, Age
1, 23
2, 45
3, 43
4, 30
5, 40
6, 32
7, 45
8, 54
9,
10, 61
11, 21
12, 39
];
 
//LOAD new table with people
People:
NoConcatenate LOAD
    PersonID,
    Person
Resident PeopleTemp;
 
Drop Table PeopleTemp;
 
//Add age and age bucket fields to the People table
Left Join (People)
LOAD
    PersonID,
    Age,
	If(IsNull(Age) or Age='', Dual('No age', 5),
	 If(Age<25, Dual('Under 25', 1),
	  If(Age>=25 and Age <35, Dual('25-34', 2),
	   If(Age>=35 and Age<50, Dual('35-49' , 3),
	    If(Age>=50, Dual('50 or over', 4)
	     ))))) as AgeBucket
Resident AgeTemp
Where Exists(PersonID);
 
DROP Table AgeTemp;

5. Cliquez sur Charger les données.

Dans le script, les champs Age et AgeBucket sont chargés uniquement si le champ PersonID a déjà été chargé dans le modèle de données.

Dans la table AgeTemp, vous observerez que les âges 11 et 12 sont répertoriés pour le champ PersonID, mais comme ces ID n'ont pas été chargés dans le modèle de données (dans la table People), ils sont exclus par la clause Where Exists(PersonID). Il est également possible d'écrire cette clause de la manière suivante : Where Exists(PersonID, PersonID).

Le résultat du script ressemble à cela :



Si aucun des ID PersonID figurant dans la table AgeTemp n'avait été chargé dans le modèle de données, alors les champs Age et AgeBucket n'auraient pas été joints à la table People. L'utilisation de la fonction Exists() permet d'empêcher la présence de données ou d'enregistrements orphelins dans le modèle de données, c.-à-d. de champs Age et AgeBucket sans aucune personne associée.

6. Créez une feuille et nommez-la.
7. Ouvrez la nouvelle feuille, puis cliquez sur Éditer la feuille.
8. Ajoutez à la feuille une table standard comportant la dimension AgeBucket et nommez la visualisation Age Groups.
9. Ajoutez un graphique à barres à la feuille avec la dimension AgeBucket et la mesure Count([AgeBucket]). Nommez la visualisation Number of people in each age group.
10. Configurez les propriétés de la table et du graphique à barres à votre convenance, puis cliquez sur Terminer.

    Votre feuille devrait à présent ressembler à cela :

    

La fonction Dual() s'avère pratique dans les scripts ou les expressions de graphique, lorsqu'il est nécessaire d'attribuer une valeur numérique à une chaîne.

Dans le script ci-dessus, vous disposez d'une application qui charge les âges. Vous avez décidé de placer ces âges dans des tranches de manière à créer ensuite des visualisations basées sur les tranches d'âges par rapport aux âges réels. Une tranche est définie pour les moins de 25 ans, une autre pour les 25-35 ans et ainsi de suite. Avec la fonction Dual(), vous pouvez attribuer une valeur numérique aux tranches d'âge. Cette valeur permettra ensuite de trier les tranches d'âge dans une liste de sélection ou un graphique. Ainsi, comme sur la feuille de l'application, la mention "No age" (Pas d'âge) figure à la fin de la liste.