FirstSortedValue, Concat, Only, Rank : les agrégations méconnues de Qlik Sense

FirstSortedValue() : la valeur qui va avec le minimum

Min(Price) donne le prix le plus bas. FirstSortedValue(Product, Price) donne le produit qui a ce prix : la fonction trie les lignes selon le deuxième argument et renvoie le premier argument de la ligne en tête.

FirstSortedValue([DISTINCT] [TOTAL [<champs>]] valeur, poids_de_tri [, rang])

• Un signe moins devant le poids inverse le tri : FirstSortedValue(Product, -Price) renvoie le produit le plus cher.
• Le rang donne la n-ième valeur : FirstSortedValue(Product, Price, 2) renvoie le deuxième moins cher.
• Le poids peut être une expression : -Price * Quantity classe par montant.

Le piège : si plusieurs lignes partagent le même poids, la fonction renvoie NULL. Elle ne choisit pas au hasard, elle refuse de choisir.

Un tiret là où l’on attendait Lampe ou Tapis : c’est une égalité, pas une donnée manquante.

Deux façons d’en sortir :

// Départager par un second critère : le prix, puis les ventes les plus fortes
FirstSortedValue(Product, Price * 10000 - Sales)                 // Mobilier : Lampe

// Afficher tous les ex aequo
Concat(Aggr(If(Min(Price) = Min(TOTAL <Category> Price), Product), Category, Product), ', ')
// Mobilier : Lampe, Tapis

La première suppose que Sales reste inférieur à 10 000 : le multiplicateur doit dépasser l’écart possible du second critère. La seconde passe par Aggr() : pour chaque produit, elle compare son prix au minimum de sa catégorie, puis Concat() assemble les produits retenus. Notre article sur les pièges d’Aggr() détaille cette table virtuelle. La version plus courte, Concat(If(Price = Min(TOTAL <Category> Price), Product)), ne fonctionne pas : testée dans Qlik Cloud, elle renvoie NULL.

Concat() : toutes les valeurs dans une chaîne

Concat() assemble les valeurs d’un champ en une seule chaîne. Dans une table par catégorie, sur la ligne Papeterie :

Concat(DISTINCT Product, ', ')            // Agenda, Cahier, Stylo
Concat(DISTINCT Product, ', ', Price)     // Stylo, Cahier, Agenda : par prix croissant

• Sans DISTINCT, une valeur présente sur plusieurs lignes apparaît plusieurs fois.
• Le troisième argument fixe l’ordre : la valeur au poids le plus bas vient en premier. Sans lui, le tri est alphabétique ; avec 0, aucun tri n’est appliqué.

Les usages ne manquent pas : le rappel des sélections dans un titre, une info-bulle qui liste les produits d’une barre, ou une liste construite dans le script pour l’injecter ensuite dans une variable.

Only() : une valeur, ou rien

Only(Category) renvoie la catégorie s’il n’y en a qu’une dans le contexte, et NULL sinon. C’est un garde-fou : là où MaxString() choisirait une valeur sans prévenir, Only() signale l’ambiguïté.

If(IsNull(Only(Category)), 'Plusieurs catégories', Only(Category))

L’aide Qlik propose un autre usage : héberger un set analysis sans rien agréger. Une table de détail, ligne à ligne, limitée au dernier mois, s’écrit avec une mesure Only({<Month = {…}>} Sales) : Only() ne calcule rien, mais porte l’ensemble.

Rank() : le rang dans la colonne

Rank(Sum(Sales)) donne le rang de la ligne : 1 pour la valeur la plus forte. Comme les fonctions inter-lignes, Rank() travaille dans le segment de colonne, et TOTAL étend le classement à toute la colonne : notre article sur les fonctions inter-lignes détaille cette notion.

La surprise vient des ex aequo.

Le rang est une valeur duale : un texte pour l’affichage, un nombre pour le calcul.

Le résultat est une valeur duale. Par défaut, les lignes à égalité affichent la plage des rangs partagés, comme 2-3. Deux arguments facultatifs règlent ce comportement :

• mode fixe le nombre : 1 donne le rang le plus bas à toutes les lignes, 3 le plus haut, 4 numérote les lignes une à une ;
• fmt fixe le texte : 1 affiche le rang bas sur toutes les lignes.

Rank(Sum(Sales), 1, 1) donne donc le classement « sportif » : 1, 2, 2, 4.

À retenir

• FirstSortedValue() renvoie la valeur associée au minimum. Un signe moins pour le maximum, un rang pour la n-ième.
• Une égalité donne NULL. Départagez par un second critère, ou listez les ex aequo avec Concat().
• Concat() assemble les valeurs. Avec DISTINCT, un séparateur et, si besoin, un poids de tri.
• Only() refuse l’ambiguïté. Une valeur s’il n’y en a qu’une, NULL sinon.
• Rank() travaille dans le segment de colonne. TOTAL pour classer toute la colonne.
• Les ex aequo de Rank() se règlent. Rank(expr, 1, 1) pour le classement 1, 2, 2, 4.

Essayer dans Qlik Sense

Ce script charge les données des schémas en INLINE. Collez-le dans l’éditeur de chargement de données d’une application vide et rechargez. Le journal affiche la liste des catégories construite par Concat() ; créez ensuite les tables indiquées en commentaire.

// Agrégations méconnues de Qlik Sense : script d'essai (HeyData)
// Collez-le dans l'éditeur de chargement de données d'une application vide, rechargez,
// puis lisez le journal et créez les tables indiquées en commentaire.
Products:
LOAD * INLINE [
Category, Product, Price, Sales
Mobilier, Chaise, 180, 60
Mobilier, Lampe, 45, 120
Mobilier, Tapis, 45, 75
Papeterie, Agenda, 12, 340
Papeterie, Cahier, 4, 340
Papeterie, Stylo, 3, 910
];

// Concat() dans le script : une liste dans une variable
CategoryList:
LOAD Concat(DISTINCT Category, ', ') as Categories
RESIDENT Products;
LET vCategories = Peek('Categories', 0, 'CategoryList');
DROP TABLE CategoryList;
TRACE Catégories : $(vCategories);
// Journal : Catégories : Mobilier, Papeterie

// Table par Category (premier schéma) :
//   FirstSortedValue(Product, Price)                                 → Mobilier : NULL, Papeterie : Stylo
//   FirstSortedValue(Product, -Price)                                → Mobilier : Chaise, Papeterie : Agenda
//   FirstSortedValue(Product, Price, 2)                              → Papeterie : Cahier
//   FirstSortedValue(Product, Price * 10000 - Sales)                 → Mobilier : Lampe
//   Concat(Aggr(If(Min(Price) = Min(TOTAL <Category> Price), Product), Category, Product), ', ')
//                                                                    → Mobilier : Lampe, Tapis ; Papeterie : Stylo
//   Concat(DISTINCT Product, ', ', Price)                            → Papeterie : Stylo, Cahier, Agenda
//
// Table par Product (second schéma) :
//   Only(Category)                                                   → la catégorie du produit
//   Rank(Sum(Sales))                                                 → Agenda et Cahier : 2-3
//   Rank(Sum(Sales), 1, 1)                                           → Agenda et Cahier : 2, Lampe : 4
//
// Indicateur, sans sélection puis avec Category = Mobilier :
//   If(IsNull(Only(Category)), 'Plusieurs catégories', Only(Category))

Sources

• Aide Qlik Cloud : FirstSortedValue, poids de tri, rang et NULL en cas d’égalité.
• Aide Qlik Cloud : Concat, séparateur et poids de tri.
• Aide Qlik Cloud : Only, valeur unique et usage avec le set analysis.
• Aide Qlik Cloud : Rank, segments de colonne et arguments mode et fmt.

Le texte et les exemples sont de HeyData. Cet article fait partie de notre série sur les fonctions et mots-clés méconnus de Qlik Sense : découvrir la série.