Class, Match, WildMatch, Pick : classer et traduire sans table de correspondance

If(Country = 'France' or Country = 'Belgique' or Country = 'Suisse', …), puis un deuxième If() imbriqué, puis un troisième : la plupart des scripts Qlik en contiennent. Ces cascades se lisent mal et se corrigent mal. Quatre fonctions, utilisables dans le script comme dans les graphiques, les remplacent : Class() pour découper en tranches, Match() et ses variantes pour tester une liste, Pick() pour choisir une valeur selon une position.

Cet article fait partie de notre série sur les fonctions et mots-clés méconnus de Qlik Sense. Il s’appuie sur l’aide Qlik ; les liens sont en fin d’article.

Match() et ses variantes : la position dans une liste

Match(Country, 'France', 'Belgique', 'Suisse') compare la valeur à chaque élément de la liste et renvoie la position du premier qui correspond : 1 pour France, 2 pour Belgique, 3 pour Suisse, 0 si rien ne correspond. Comme 0 vaut faux et tout autre nombre vrai, la fonction remplace directement une suite de or :

If(Match(Country, 'France', 'Belgique', 'Suisse'), 'Europe', 'Autre')

Trois variantes se distinguent par leur façon de comparer :

Match() tient compte de la casse : france ne correspond pas à 'France' ;
MixMatch() l’ignore ;
WildMatch() l’ignore aussi, et accepte les jokers : * pour une suite de caractères, ? pour un seul caractère.

Une table Customers avec les champs Country et Ref, et les résultats de Match, MixMatch et WildMatch. France, FR-001 : 1, 1, 1. france en minuscules, fr-002, surlignée : 0, 1, 1. Belgique, BE-001 : 2, 2, 2. Suisse, CH-001 : 3, 3, 0. Canada, CA-001 : 0, 0, 0. En dessous, les trois expressions utilisées.

Une majuscule manquante suffit à faire échouer Match().

Sur des données saisies à la main, MixMatch() est souvent le choix le plus sûr. Ces fonctions servent aussi à filtrer au chargement :

Europe:
NOCONCATENATE LOAD * RESIDENT Customers
WHERE MixMatch(Country, 'France', 'Belgique', 'Suisse');

Autre usage, moins connu : le tri personnalisé. Dans les propriétés d’un graphique, l’option Trier par expression accepte Match(Country, 'Suisse', 'Belgique', 'France') : les pays s’affichent dans l’ordre de la liste, quel que soit l’ordre alphabétique. En tri croissant, les valeurs absentes de la liste, qui valent 0, passent en tête.

Pick() : choisir selon une position

Pick(n, expr1, expr2, …) renvoie la n-ième expression de la liste. Seule, elle traduit un code en libellé :

Pick(CategoryCode, 'Électronique', 'Accessoires', 'Bureautique')

Combinée à Match(), elle devient l’équivalent d’un CASE : Match() trouve la position, Pick() renvoie la valeur correspondante. L’aide Qlik signale une précaution : n doit être compris entre 1 et le nombre d’expressions, or Match() renvoie 0 quand rien ne correspond. D’où le 1 +, qui réserve la première position à une valeur par défaut.

La table Customers avec les colonnes Country, MixMatch, 1 + MixMatch et Pick. France et france : 1, 2, Zone euro. Belgique : 2, 3, Zone euro. Suisse : 3, 4, Europe hors euro. Canada, surligné : 0, 1, Hors Europe. En dessous, l’expression complète.

Canada ne figure pas dans la liste : la valeur par défaut, en première position, prend le relais.

Le même duo choisit un calcul et pas seulement un libellé : notre article sur les dimensions synthétiques l’utilise pour construire une table d’indicateurs.

Au-delà d’une dizaine de valeurs, ou quand la correspondance est tenue par les métiers dans un fichier, une table de correspondance chargée avec MAPPING LOAD et appliquée par ApplyMap() reste plus facile à maintenir.

Class() : des tranches en une fonction

Class(Amount, 50) range un montant dans une tranche de largeur 50 et renvoie une valeur duale :

le texte décrit la tranche, sous la forme 50 <= x < 100 ;
le nombre est la borne basse, 50.

La table Customers avec les colonnes Customer, Amount, Class(Amount, 50) et Num(Class(Amount, 50)). Alpha, 35 : 0 <= x < 50 et 0. Beta, 120 : 100 <= x < 150 et 100. Gamma, 75 : 50 <= x < 100 et 50. Delta, 160 : 150 <= x < 200 et 150. Epsilon, 20 : 0 <= x < 50 et 0.

Le texte pour l’affichage, la borne basse pour le tri.

Capture d’un graphique en barres Qlik Cloud du nombre de clients, Count(Customer), par tranche Class(Amount, 50). Les tranches se suivent dans l’ordre des montants : 0 <= x < 50 avec 2 clients, puis 50 <= x < 100, 100 <= x < 150 et 150 <= x < 200 avec 1 client chacune.

Dans Qlik Cloud, triées par valeur numérique : 100 <= x < 150 vient après 50 <= x < 100, et non avant comme dans un tri alphabétique.

Grâce au nombre, une dimension construite avec Class() se trie dans l’ordre des montants, et non dans l’ordre alphabétique de ses libellés : notre article sur Dual() explique ce mécanisme. Deux arguments facultatifs ajustent le résultat :

Class(Amount, 50, 'montant')      // 50 <= montant < 100 : un libellé à la place du x
Class(Amount, 50, 'x', 25)        // des tranches décalées de 25 : 25 <= x < 75, 75 <= x < 125…

Calculée dans le script plutôt que dans une dimension calculée, la tranche devient un champ comme un autre : sélectionnable, et calculée une seule fois.

À retenir

Match() renvoie une position. 0 si rien ne correspond, ce qui en fait une condition prête à l’emploi.
Trois variantes, trois comparaisons. Match() tient compte de la casse, MixMatch() non, WildMatch() accepte les jokers.
Match() trie aussi. Dans un tri par expression, il impose l’ordre de la liste.
Pick(1 + Match()) traduit. Le 1 + réserve la première position à la valeur par défaut.
Class() découpe en tranches. Un texte lisible, et la borne basse comme nombre pour le tri.
Une table au-delà de quelques valeurs. MAPPING LOAD et ApplyMap() se maintiennent mieux.

Essayer dans Qlik Sense

Ce script charge les données des schémas en INLINE et calcule la zone, la tranche et le contrôle de référence dès le chargement. Collez-le dans l’éditeur de chargement de données d’une application vide et rechargez. Créez ensuite la table indiquée en commentaire.

// Class, Match, WildMatch et Pick dans Qlik Sense : script d'essai (HeyData)
// Collez-le dans l'éditeur de chargement de données d'une application vide, rechargez,
// puis créez la table indiquée en commentaire.
Customers:
LOAD *,
     Pick(1 + MixMatch(Country, 'France', 'Belgique', 'Suisse'),
          'Hors Europe', 'Zone euro', 'Zone euro', 'Europe hors euro') as Zone,
     Class(Amount, 50) as AmountBand,
     If(WildMatch(Ref, 'FR-*', 'BE-*'), 'Oui', 'Non') as RefFrBe;
LOAD * INLINE [
Customer, Country, Ref, Amount
Alpha, France, FR-001, 35
Beta, france, fr-002, 120
Gamma, Belgique, BE-001, 75
Delta, Suisse, CH-001, 160
Epsilon, Canada, CA-001, 20
];

// Table avec les dimensions Customer, Country, Zone, AmountBand et RefFrBe, et les mesures :
//   Only(Match(Country, 'France', 'Belgique', 'Suisse'))      → Beta : 0 (casse)
//   Only(MixMatch(Country, 'France', 'Belgique', 'Suisse'))   → Beta : 1
//   Only(WildMatch(Ref, 'FR-*', 'BE-*'))                      → Beta : 1, Delta : 0
//   Only(Num(AmountBand))                                     → la borne basse de la tranche
//
// Tri personnalisé : dans le tri de la dimension Country, choisissez Trier par expression
//   MixMatch(Country, 'Suisse', 'Belgique', 'France')        → Canada (0), Suisse, Belgique, France et france

Sources

Aide Qlik Cloud : Match, position, sensibilité à la casse et tri personnalisé.
Aide Qlik Cloud : MixMatch et WildMatch, comparaison sans casse et jokers.
Aide Qlik Cloud : Pick, y compris l’association de Pick et Match avec une valeur par défaut.
Aide Qlik Cloud : Class, largeur, libellé et décalage des tranches.

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.