NULL, zéro, chaîne vide : bien gérer l’absence de valeur dans Qlik Sense

Le problème

NULL n’est pas une valeur : c’est l’absence de valeur. Il n’a pas de type et n’est donc pas un dual (ni texte, ni nombre). Tout champ peut en contenir. Quatre propriétés en découlent :

• une expression contenant un NULL ne plante pas, le NULL se propage dans le calcul ;
• un NULL ne sert jamais de clé entre deux tables ;
• un NULL n’apparaît pas dans un volet de filtre et ne se sélectionne pas ;
• sélectionner toutes les valeurs d’un champ ne revient donc pas à n’en sélectionner aucune : la première sélection exclut les lignes où ce champ est NULL.

Le piège tient surtout à ce que le vide prend plusieurs formes, que Qlik Sense ne traite pas de la même façon.

Cinq formes de vide

Chaque forme de vide réagit différemment aux fonctions d’agrégation.

• Le zéro est une vraie valeur. IsNull() renvoie False, et la ligne entre dans Count() comme dans Avg().
• La chaîne vide, ou composée d’espaces, reste une chaîne. Count() la compte, Sum() et Avg() l’ignorent puisqu’elle n’est pas numérique. Attention aux blancs : Trim() ne retire que l’espace ordinaire Chr(32), pas l’espace insécable Chr(160), la tabulation Chr(9) ni l’espace idéographique Chr(12288).
• Le NULL réel occupe une cellule d’une ligne qui existe. Il vient d’une base lue en ODBC ou OLE DB, d’un Join ou d’un Concatenate (dans le script Qlik comme en SQL), ou d’une fonction qui renvoie NULL : Null(), Only(), un If() à deux paramètres. Un fichier texte, lui, n’en contient pas par lui-même.

Les deux dernières formes sont des valeurs manquantes : aucune ligne n’existe, donc aucune cellule ne peut être marquée NULL.

Type 1 : une clé sans correspondance. Type 2 : une combinaison de dimensions sans données.

Le type 1 naît d’une différence de cardinalité entre deux tables : un client existe dans Customers mais n’a aucune ligne dans Orders. Pour calculer une visualisation, le moteur construit une table virtuelle où ces champs valent NULL : Count(OrderID) donne 0, NullCount(OrderID) donne 1.

Le type 2 apparaît dans un tableau croisé : la combinaison 2012 × Q3 n’existe pas dans les données, une seule table suffit. Le moteur ne parcourt que les lignes existantes ; la cellule n’est jamais calculée et affiche un tiret.

Comment NULL se propage

Qlik Sense applique une logique à trois valeurs (logique de Kleene) : True, False et NULL, au sens d’« indéterminé ».

AND et OR en logique à trois valeurs, et une sélection d’opérateurs et de fonctions.

Les règles utiles au quotidien :

• If() dont la condition vaut NULL renvoie la branche sinon, pas NULL.
• = renvoie False dès qu’un seul opérande est NULL : Field = Null() n’est donc jamais vrai. Testez avec IsNull(Field).
• >, <, >=, <= et l’arithmétique renvoient NULL ; les fonctions Range*() ignorent les NULL.
• LIKE ne renvoie NULL que si le motif est NULL ; & ne renvoie NULL que si les deux côtés le sont.
• Les agrégations ignorent les NULL, sauf NullCount(). Only() renvoie NULL s’il n’y a aucune valeur ou plusieurs valeurs distinctes.

Ce que montrent les visualisations

• Dans un volet de filtre, NULL ne figure jamais. Si toutes les valeurs passent en gris après une sélection, la réponse est NULL. Une ligne vide que l’on peut cliquer n’est pas un NULL, mais une chaîne vide ou des blancs.
• Dans un tableau, NULL s’affiche par défaut sous forme de tiret, visible mais non cliquable.
• Côté dimension, une commande sans client produit une ligne « - ». Pour la masquer, décochez Inclure les valeurs nulles dans les propriétés de la dimension.
• Côté mesure, Sum() et Count() renvoient 0 pour un client sans commande, ce qui est juste. L’option Inclure les valeurs zéro, dans les options de traitement des données, masque ces lignes.
• Pour le type 2, la cellule reste un tiret, jamais un 0. QlikView proposait de « peupler les cellules manquantes » ; nous ne connaissons pas d’option équivalente dans le tableau croisé Qlik Sense. Masquer les NULL d’une dimension masque aussi, par inférence logique, les clients qui n’ont rien acheté.

Dans Qlik Cloud : PARIS n’a pas de commande. OrderID affiche un tiret, Count() renvoie 0 et NullCount() compte le NULL.

Rendre le vide visible dans le script

Les variables système de QlikView existent toujours dans l’éditeur de chargement de données. NullInterpret convertit des blancs ou un symbole en NULL :

SET NullInterpret = ;    // empty fields in text files
SET NullInterpret = '';  // empty cells in Excel files

Contacts:
LOAD
    PersonID,
    If(Len(Trim(Phone)) > 0, Phone) as Phone   // blanks become NULL
FROM [lib://Sources/contacts.csv]
(txt, utf8, embedded labels, delimiter is ',');

À l’inverse, NullDisplay donne un libellé aux NULL venus d’ODBC ou OLE DB, et NullAsValue avec NullValue les remplace dans les LOAD suivants. Deux limites : un chargement QVD optimisé ne transforme pas les NULL, et ceux créés par Join ou Concatenate ne sont convertis qu’en partie. La solution : une seconde passe en Resident.

NullAsValue *;
SET NullValue = '<NULL>';

Temp:
LOAD * FROM [lib://Sources/a.qvd] (qvd);
Concatenate (Temp)
LOAD * FROM [lib://Sources/b.qvd] (qvd);

Data:
NoConcatenate
LOAD * Resident Temp;
DROP TABLE Temp;

Pour tester le vide, IsNull() ignore les chaînes vides. Len(Trim(Field)) = 0 couvre NULL, chaîne vide, espaces et valeurs manquantes ; Len(PurgeChar(Field, Chr(9) & Chr(32) & Chr(160) & Chr(12288))) = 0 couvre tous les blancs. Dans le script, Exists() détecte une valeur manquante en comparant à un champ déjà chargé.

Sélectionner ce qui n’existe pas

Un NULL ne se sélectionne pas : il faut passer par un autre champ. Trois méthodes.

Sélectionner les exclus. Sélectionnez toutes les valeurs de Phone, puis, dans PersonID, choisissez Sélectionner les valeurs exclues. La même démarche répond à « quels clients n’ont pas acheté ce produit ? ». Un bouton dont l’action sélectionne les valeurs exclues épargne l’opération aux utilisateurs.

Rechercher par expression. Dans le volet de filtre PersonID, saisissez =NullCount(Phone)>0. Évitez =IsNull(Phone) : sans agrégation, le moteur évalue IsNull(Only(Phone)), qui renvoie True pour une personne ayant deux numéros et False pour une personne ayant un numéro et une ligne vide.

Préparer un champ indicateur. C’est la solution la plus lisible pour les utilisateurs :

People:
LOAD
    PersonID,
    If(Len(Trim(Phone)) > 0, 'Yes', 'No') as [Has phone]
FROM [lib://Sources/people.qvd] (qvd);

Orders:   // must be loaded first for Exists() to work
LOAD CustomerID, OrderID, Amount
FROM [lib://Sources/orders.qvd] (qvd);

Customers:
LOAD
    CustomerID,
    CompanyName,
    If(Exists(CustomerID), Dual('Yes', True()), Dual('No', False())) as [Has orders]
FROM [lib://Sources/customers.qvd] (qvd);

Le Dual() permet en plus d’utiliser [Has orders] comme booléen dans une condition.

NULL et set analysis

Les mêmes idées s’appliquent dans les expressions :

// Personnes sans numéro de téléphone
Concat({<PersonID = {"=NullCount(Phone)>0"}>} DISTINCT PersonID, ', ')

// Clients sans aucune commande : exclus de l'ensemble des clients qui ont un OrderID
Count({<CustomerID = E({<OrderID = {"*"}>})>} DISTINCT CustomerID)

// Clients qui n'ont jamais acheté de chaussures
Count({<CustomerID = E({1<Product = {'Shoe'}>})>} DISTINCT CustomerID)

{"*"} retient toutes les valeurs sauf NULL. Un ensemble vide, <Product = {}>, désigne les lignes sans produit ; à ne pas confondre avec <Product = >, qui efface simplement la sélection du champ. P() et E() renvoient les valeurs possibles et exclues.

À retenir

• NULL n’est ni 0 ni ”. Zéro et chaîne vide sont des valeurs ; seul NULL renvoie True à IsNull().
• Deux sortes de valeurs manquantes. Une clé sans correspondance (type 1) vaut NULL ; une combinaison absente (type 2) n’est jamais calculée.
• Pas de test par =. Field = Null() n’est jamais vrai : utilisez IsNull() ou Len(Trim()) = 0.
• Sélectionner par un autre champ. Valeurs exclues, recherche =NullCount(Field)>0 ou champ indicateur.
• Gare aux chargements optimisés. NullAsValue n’agit ni sur un QVD optimisé ni complètement après Join ou Concatenate.

Essayer dans Qlik Sense

Ce script reprend les exemples de l’article avec les données des schémas, chargées en INLINE : il ne dépend d’aucun fichier ni d’aucune base. Collez-le dans l’éditeur de chargement de données d’une application vide, choisissez l’exemple avec la variable vExemple, puis rechargez.

// NULL et absence de valeur dans Qlik Sense : script d'essai
// D'après « NULL and Nothing », Technical Brief d'Henric Cronström (Qlik, 2013), adapté par HeyData.
// Collez-le dans l'éditeur de chargement de données d'une application vide, choisissez
// l'exemple, rechargez, puis construisez les tableaux indiqués.
SET vExemple = 1;   // 1 à 4

IF $(vExemple) = 1 THEN
    // Exemple 1 : valeurs manquantes de type 1 et de type 2 (deuxième schéma)
    Orders:   // chargée en premier pour que Exists() fonctionne
    LOAD * INLINE [
    CustomerID, OrderID
    BOLID, 10326
    BOLID, 10801
    BOLID, 10970
    GROSR, 10268
    GROSR, 10785
    ];

    Customers:
    LOAD CustomerID,
        If(Exists(CustomerID), Dual('Yes', True()), Dual('No', False())) as [Has orders]
    INLINE [
    CustomerID
    BOLID
    GROSR
    PARIS
    ];

    Data:
    LOAD * INLINE [
    Year, Quarter, Amount
    2011, Q1, 120000
    2011, Q2, 110000
    2011, Q3, 130000
    2011, Q4, 190000
    2012, Q1, 125000
    2012, Q2, 140000
    ];
    // Type 1 : tableau CustomerID, Count(OrderID), NullCount(OrderID) → PARIS : 0 et 1.
    // Type 2 : tableau croisé Year × Quarter, Sum(Amount) → 2012 Q3 et Q4 affichent « - ».
    // Clients sans commande : Count({<CustomerID = E({<OrderID = {"*"}>})>} DISTINCT CustomerID) → 1.
END IF

IF $(vExemple) = 2 THEN
    // Exemple 2 : les blancs deviennent NULL, et un champ indicateur les rend sélectionnables
    People:
    LOAD PersonID,
        If(Len(Trim(Phone)) > 0, Phone)        as Phone,
        If(Len(Trim(Phone)) > 0, 'Yes', 'No')  as [Has phone]
    INLINE [
    PersonID, Phone
    1, 0601020304
    2,
    3, 0605060708
    4, 0611121314
    4,
    ];
    // Recherche « =NullCount(Phone)>0 » dans PersonID → 2 et 4.
    // Avec « =IsNull(Phone) », 4 n'est pas trouvé : IsNull(Only(Phone)) vaut False.
END IF

IF $(vExemple) = 3 THEN
    // Exemple 3 : les NULL nés d'un Concatenate, rendus visibles par NullAsValue
    NullAsValue *;
    SET NullValue = '<NULL>';

    Temp:
    LOAD * INLINE [
    ID, Phone
    1, 0601020304
    ];
    Concatenate (Temp)
    LOAD * INLINE [
    ID, Email
    2, contact@example.com
    ];

    Data:
    NoConcatenate
    LOAD * Resident Temp;
    DROP TABLE Temp;
    // Phone et Email valent '<NULL>' là où la table d'origine n'avait pas la colonne.
END IF

IF $(vExemple) = 4 THEN
    // Exemple 4 : comment NULL se propage (troisième schéma). True = -1, False = 0.
    Tmp:
    LOAD 'NOT NULL'          as Expression, Not Null()             as Value AUTOGENERATE 1;
    Concatenate (Tmp) LOAD 'If(NULL, ''a'', ''b'')' as Expression, If(Null(), 'a', 'b') as Value AUTOGENERATE 1;
    Concatenate (Tmp) LOAD 'NULL = NULL'        as Expression, Null() = Null()      as Value AUTOGENERATE 1;
    Concatenate (Tmp) LOAD '1 = NULL'           as Expression, 1 = Null()           as Value AUTOGENERATE 1;
    Concatenate (Tmp) LOAD '1 <> NULL'          as Expression, 1 <> Null()          as Value AUTOGENERATE 1;
    Concatenate (Tmp) LOAD 'NULL > 1'           as Expression, Null() > 1           as Value AUTOGENERATE 1;
    Concatenate (Tmp) LOAD 'NULL + 5'           as Expression, Null() + 5           as Value AUTOGENERATE 1;
    Concatenate (Tmp) LOAD 'RangeSum(NULL, 5)'  as Expression, RangeSum(Null(), 5) as Value AUTOGENERATE 1;
    Concatenate (Tmp) LOAD '''xyz'' & NULL'     as Expression, 'xyz' & Null()       as Value AUTOGENERATE 1;
    Concatenate (Tmp) LOAD 'Len(NULL)'          as Expression, Len(Null())          as Value AUTOGENERATE 1;

    Propagation:
    NoConcatenate
    LOAD Expression, If(IsNull(Value), 'NULL', Text(Value)) as Result
    Resident Tmp;
    DROP TABLE Tmp;
    // Tableau Expression, Result : comparez avec le schéma.
END IF

Source

« NULL and Nothing », QlikView Technical Brief, Henric Cronström (alias « hic » sur Qlik Community), 26 avril 2013, publié sur Qlik Community dans la rubrique QlikView Documents. Tout le mérite du contenu original lui revient ; l’adaptation à Qlik Sense et ses éventuelles erreurs sont de HeyData. Cet article fait partie de notre relecture des Technical Briefs : découvrir la série.