Le préfixe Generic dans Qlik Sense : des données attribut-valeur aux dimensions

Sans Generic : une seule colonne pour tout

Avec la table telle quelle, la couleur n’est qu’une valeur parmi d’autres du champ Value. Pour compter les produits rouges, il faut filtrer l’attribut dans chaque expression :

Count({<Attribute = {'Color'}, Value = {'Red'}>} DISTINCT ProductID)

Ça fonctionne pour une mesure ponctuelle, mais pas pour une dimension : on ne peut pas mettre « Color » en ligne et « Size » en colonne d’un tableau croisé.

Generic : une table par attribut

Placé devant un LOAD de trois champs, la clé, l’attribut et la valeur, Generic crée une table par valeur distincte de l’attribut. Chaque table porte la clé et un champ nommé d’après l’attribut. Le nom de la table combine l’étiquette du LOAD et l’attribut : Products.Color, Products.Size, Products.Status.

Trois attributs, trois tables reliées par ProductID.

Products:
Generic
LOAD * INLINE [
ProductID, Attribute, Value
P1, Color, Red
P1, Size, M
P1, Status, Active
P2, Color, Blue
P2, Size, L
P3, Color, Red
P3, Status, Discontinued
];

Les tables sont reliées par ProductID. Color, Size et Status sont désormais des champs ordinaires : dimensions, filtres, sélections. Chaque table ne contient que les produits qui ont l’attribut : P2 n’a pas de statut, il est simplement absent de Products.Status.

Generic est l’inverse de CrossTable : l’un met des lignes en colonnes, l’autre des colonnes en lignes.

Regrouper en une seule table

Ce modèle éclaté est juste, et économe en mémoire. Mais avec trente attributs, la visionneuse du modèle de données affiche trente tables. Dans le billet « The Generic Load », Henric Cronström propose une boucle qui rattache ces tables, par LEFT JOIN, à une table de référence. En voici une version compacte :

Products:
LOAD DISTINCT ProductID RESIDENT TmpAttributes;

FOR i = NoOfTables() - 1 TO 0 STEP -1
    LET vTable = TableName($(i));
    IF WildMatch('$(vTable)', 'Attr.*') THEN
        LEFT JOIN (Products) LOAD * RESIDENT [$(vTable)];
        DROP TABLE [$(vTable)];
    END IF
NEXT i

La boucle part de la dernière table, car chaque DROP TABLE décale les numéros des suivantes. Le préfixe de la table (Attr.) permet de ne traiter que les tables produites par Generic. La première table, Products, sert de référence : tous les produits y figurent, même ceux qui n’ont aucun attribut.

Après regroupement, un attribut absent vaut NULL.

L’aide Qlik décrit une variante avec OUTER JOIN et un RENAME TABLE. La boucle avec LEFT JOIN a l’avantage de traiter n’importe quel nombre d’attributs sans les nommer.

Les points de vigilance

• Une valeur par clé et par attribut. Si un produit a deux couleurs, Products.Color a deux lignes pour lui. Après le regroupement, ce produit apparaît deux fois, et les mesures portées par la table sont dupliquées. Vérifiez la source, ou gardez ces attributs multiples dans leur propre table.
• Les noms d’attribut deviennent des noms de champs. Un attribut « Date de création » donne un champ [Date de création]. Nettoyez les libellés avant le Generic si besoin, avec un mapping.
• Les types suivent les valeurs. Toutes les valeurs passent par le même champ Value ; une date ou un nombre peut arriver en texte. Convertissez-les après le regroupement.
• Garder le modèle éclaté reste possible. Pour quelques attributs, les tables séparées fonctionnent très bien : le regroupement est un confort de lecture, pas une obligation.

À retenir

• Du modèle attribut-valeur aux champs. Generic crée un champ par attribut, utilisable comme dimension.
• Une table par attribut. Nommée Étiquette.Attribut, reliée aux autres par la clé.
• L’inverse de CrossTable. Generic met des lignes en colonnes.
• Regroupez avec une boucle. LEFT JOIN de chaque table sur une table de référence, en partant de la dernière.
• Une valeur par attribut. Un attribut multiple duplique les lignes au regroupement.

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.

// Le préfixe Generic dans Qlik Sense : script d'essai (HeyData)
// Collez-le dans l'éditeur de chargement de données d'une application vide, choisissez
// l'exemple, rechargez, puis ouvrez la visionneuse du modèle de données.
SET vExemple = 1;   // 1 à 3

TmpAttributes:
LOAD * INLINE [
ProductID, Attribute, Value
P1, Color, Red
P1, Size, M
P1, Status, Active
P2, Color, Blue
P2, Size, L
P3, Color, Red
P3, Status, Discontinued
];

IF $(vExemple) = 1 THEN
    // Exemple 1 : sans Generic, une seule table attribut-valeur
    // Tableau : dimension ProductID, mesure Count({<Attribute = {'Color'}, Value = {'Red'}>} DISTINCT ProductID)
    RENAME TABLE TmpAttributes TO ProductAttributes;
END IF

IF $(vExemple) = 2 THEN
    // Exemple 2 : Generic crée une table par attribut (premier schéma)
    Products:
    Generic
    LOAD ProductID, Attribute, Value RESIDENT TmpAttributes;
    DROP TABLE TmpAttributes;
    // 3 tables : Products.Color, Products.Size, Products.Status.
END IF

IF $(vExemple) = 3 THEN
    // Exemple 3 : regrouper les tables en une seule (deuxième schéma)
    Attr:
    Generic
    LOAD ProductID, Attribute, Value RESIDENT TmpAttributes;

    Products:
    LOAD DISTINCT ProductID RESIDENT TmpAttributes;
    DROP TABLE TmpAttributes;

    FOR i = NoOfTables() - 1 TO 0 STEP -1
        LET vTable = TableName($(i));
        IF WildMatch('$(vTable)', 'Attr.*') THEN
            LEFT JOIN (Products) LOAD * RESIDENT [$(vTable)];
            DROP TABLE [$(vTable)];
        END IF
    NEXT i
    // 1 table, 3 lignes : P2 n'a pas de Status, P3 n'a pas de Size.
END IF

Sources

• Aide Qlik Cloud : Generic, syntaxe, exemples et regroupement par OUTER JOIN.
• Henric Cronström, Qlik Design Blog : The Generic Load, mars 2014.

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.