Générer les données manquantes dans Qlik Sense

Une source ne contient pas toujours tout ce que l’analyse demande. Un taux n’est stocké qu’au jour où il change, un contrat ne porte que ses dates de début et de fin, un calendrier n’existe nulle part. Dans Qlik Sense, la réponse se trouve dans le script : on génère soi-même les lignes qui manquent.

Cet article synthétise et adapte à Qlik Sense le Technical Brief « Generating Missing Data » d’Henric Cronström (Qlik, février 2013). Ses documents techniques restent des références : nous les relisons avec les outils d’aujourd’hui.

Le problème

Trois situations reviennent sans cesse dans nos projets :

une table entière manque, alors qu’elle se déduit d’autres données (le calendrier en est l’exemple type) ;
des lignes manquent : la logique métier dit qu’elles existent, et l’utilisateur veut pouvoir cliquer dessus. Une seule ligne source doit parfois en produire plusieurs ;
les lignes existent, mais un champ est vide et doit reprendre la valeur de la ligne précédente.

Les cas concrets suivent : taux de change connus seulement aux dates de changement, soldes de stock par produit, contrats valables sur une période, calendrier principal. Tout se traite dans l’éditeur de chargement de données. Le Data Manager crée bien des champs de calendrier dérivés, mais il ne fabrique pas, à notre connaissance, les dates absentes des données.

Créer une table : Resident ou AutoGenerate

La voie la plus simple consiste à relire une table déjà chargée avec un LOAD DISTINCT … RESIDENT. Elle a une limite : seules les valeurs présentes dans la table de faits apparaissent. Un jour sans vente n’aura pas de ligne dans le calendrier.

Pour couvrir toute une plage, AUTOGENERATE crée des lignes à partir de rien. Aucune source n’est lue : chaque valeur est calculée, typiquement avec RecNo() ou Rand(). On commence par chercher les bornes, puis on génère chaque date de l’intervalle :

MinMaxDate:
LOAD Min(Date) as MinDate,
     Max(Date) as MaxDate
RESIDENT Transactions;

LET vMinDate = Peek('MinDate', -1, 'MinMaxDate') - 1;
LET vMaxDate = Peek('MaxDate', -1, 'MinMaxDate');
DROP TABLE MinMaxDate;

MasterCalendar:
LOAD Date,
     Year(Date)  as Year,
     Month(Date) as Month,
     Day(Date)   as Day;
LOAD Date($(vMinDate) + RecNo()) as Date
AUTOGENERATE $(vMaxDate) - $(vMinDate);

Le LOAD du haut est un preceding load : il dérive les champs de calendrier de la seule clé Date. Les bornes peuvent aussi être fixées autrement, par exemple de début d’année dernière à fin d’année en cours avec YearStart(Today(), -1) et YearEnd(Today()).

Propager une valeur vers le bas avec Peek

Henric Cronström qualifie Peek() de « my preferred solution » pour ce besoin. Sans autre argument, la fonction renvoie la valeur d’un champ dans la dernière ligne déjà chargée. Pour remplacer un NULL par la valeur du dessus :

If(IsNull(Rate), Peek('Rate'), Rate) as Rate

Peek() lit la table en sortie, donc une valeur déjà propagée l’est encore à la ligne suivante. Previous(), elle, lit l’enregistrement source précédent, qui reste vide : elle ne convient pas ici.

Deux conditions encadrent la technique. Les lignes doivent être triées, et ORDER BY n’est accepté que sur un LOAD … RESIDENT. Il faut donc souvent deux passes. Et si la propagation doit rester dans un groupe, on compare la clé à celle de la ligne précédente. Exemple avec un cumul par produit :

TempData:
LOAD Product, Date, Amount FROM [lib://Data/Sales.qvd] (qvd);

Data:
LOAD Product, Date, Amount,
     If(Product = Peek('Product'),
        RangeSum(Amount, Peek('AccumulatedAmount')),
        RangeSum(Amount)) as AccumulatedAmount
RESIDENT TempData
ORDER BY Product, Date;

DROP TABLE TempData;

RangeSum() traite NULL comme zéro, là où l’addition classique renverrait NULL.

Combler les dates d’une table de taux

Les deux briques se combinent. La table des taux ne liste que les jours de changement. On génère toutes les dates de la plage, on les joint aux taux, puis on trie et on propage.

TempRates:
LOAD Date, Rate FROM [lib://Data/Rates.qvd] (qvd);

MinMaxDate:
LOAD Min(Date) as MinDate, Max(Date) as MaxDate RESIDENT TempRates;
LET vMinDate = Peek('MinDate', -1, 'MinMaxDate') - 1;
LET vMaxDate = Peek('MaxDate', -1, 'MinMaxDate');
DROP TABLE MinMaxDate;

JOIN (TempRates)
LOAD Date($(vMinDate) + RecNo()) as Date
AUTOGENERATE $(vMaxDate) - $(vMinDate);

Rates:
NOCONCATENATE
LOAD Date,
     If(IsNull(Rate), Peek('Rate'), Rate) as Rate
RESIDENT TempRates
ORDER BY Date;

DROP TABLE TempRates;

NOCONCATENATE est indispensable : sans lui, Rates ayant les mêmes champs que TempRates, Qlik l’ajouterait à la table temporaire, qui serait ensuite supprimée avec.

Join ajoute les dates manquantes, Peek leur donne le dernier taux connu.

Boucler dans le LOAD : While et SubField

Une ligne source doit parfois en produire plusieurs. La clause WHILE rejoue chaque enregistrement tant que sa condition reste vraie, et IterNo() donne le numéro du passage. Pour des contrats d’assurance, on obtient une ligne par jour de validité :

Policies:
LOAD PolicyID, BirthDate, FromDate, ToDate
FROM [lib://Data/Policies.qvd] (qvd);

Policies_x_Dates:
LOAD PolicyID,
     Age(FromDate + IterNo() - 1, BirthDate) as Age,
     Date(FromDate + IterNo() - 1) as ReferenceDate
RESIDENT Policies
WHILE IterNo() <= ToDate - FromDate + 1;

Sélectionner une ReferenceDate répond alors à « combien de contrats valides ce jour-là ? ». L’âge de l’assuré dépend de la date de référence : il va dans la table générée. La visionneuse du modèle de données doit montrer les deux tables liées par PolicyID.

À gauche, une seule ligne de contrat P01 valable du 1er au 4 mars 2013. Une flèche portant la condition While IterNo() <= ToDate - FromDate + 1 mène à droite vers quatre lignes générées, surlignées en vert menthe, numérotées de 1 à 4 par IterNo() avec les dates de référence du 1er au 4 mars.

Une ligne de contrat devient une ligne par jour de validité.

Un LOAD ne peut pas porter à la fois WHERE et WHILE. Pour combiner les deux, on empile deux LOAD : le filtre placé dans le LOAD du bas s’applique avant la boucle, celui du haut après.

Quand la source contient une liste de valeurs dans un seul champ, SubField() à deux paramètres boucle sur chaque élément :

Employees_x_Skills:
LOAD [Emp No],
     Trim(SubField(Skills, ',')) as Skill
RESIDENT Employees;

Avec un troisième paramètre, la fonction renvoie un seul élément et ne boucle plus.

Toutes les combinaisons : produit cartésien et IntervalMatch

Un JOIN entre deux tables sans champ commun produit toutes les combinaisons, comme un produit cartésien SQL. C’est la clé du stock d’entrepôt : les soldes ne sont stockés qu’aux changements, et chaque produit a ses propres dates.

// A. Existing balances, with a product-date key
TempBalances:
LOAD ProductID, Date, Balance,
     ProductID & '|' & Num(Date) as Product_x_DateID
FROM [lib://Data/ProductBalances.qvd] (qvd);

MinMaxDate:
LOAD Min(Date) as MinDate, Max(Date) as MaxDate RESIDENT TempBalances;
LET vMinDate = Peek('MinDate', -1, 'MinMaxDate') - 1;
LET vMaxDate = Peek('MaxDate', -1, 'MinMaxDate');
DROP TABLE MinMaxDate;

// B. Every product-date combination
TempProduct_x_Dates:
LOAD DISTINCT ProductID RESIDENT TempBalances;
JOIN (TempProduct_x_Dates)
LOAD Date($(vMinDate) + RecNo()) as Date
AUTOGENERATE $(vMaxDate) - $(vMinDate);

// C. Append only the missing combinations
CONCATENATE (TempBalances)
LOAD * WHERE NOT Exists(Product_x_DateID);
LOAD ProductID, Date,
     ProductID & '|' & Num(Date) as Product_x_DateID
RESIDENT TempProduct_x_Dates;

// D. Sort and carry the last balance down, per product
ProductBalances:
NOCONCATENATE
LOAD ProductID, Date,
     If(ProductID = Peek('ProductID') and IsNull(Balance),
        Peek('Balance'),
        RangeSum(Balance)) as Balance
RESIDENT TempBalances
ORDER BY ProductID, Date;

// E. Clean up
DROP TABLES TempProduct_x_Dates, TempBalances;

Le test sur ProductID empêche un produit d’hériter du solde du précédent. Un produit sans solde en début de plage obtient 0, grâce à RangeSum().

Le produit cartésien fournit chaque couple produit × date, Peek complète les soldes.

Cas particulier : rattacher une date d’événement à des intervalles. Là où SQL écrirait un BETWEEN, Qlik Sense propose le préfixe IntervalMatch, qui génère une table pont :

IntervalMatchBridge:
IntervalMatch (Date)
LOAD DISTINCT BeginDate, EndDate RESIDENT Intervals;

La visionneuse du modèle de données affichera une clé synthétique sur BeginDate et EndDate. Ici, elle est normale : le couple identifie l’intervalle, et c’est la modélisation la plus efficace.

Bonus : des simulations

Ces techniques suffisent pour une simulation de Monte-Carlo. AUTOGENERATE crée par exemple 100 000 lancers de deux dés avec Ceil(Rand() * 6). Une visualisation calcule ensuite Count(ThrowNo) / Count(TOTAL ThrowNo) par somme des dés. Pour le cumul, l’original utilisait RangeSum(Above(…)), toujours valable dans une table. Les graphiques Qlik Sense proposent aussi le modificateur Accumulation. Le document va jusqu’à simuler des mains de poker avec une boucle FOR … NEXT et des GROUP BY. Gardez en tête l’incertitude statistique : relancer le script plusieurs fois en donne une idée.

À retenir

AutoGenerate plutôt que Resident. Un calendrier tiré des faits oublie les jours sans activité ; une plage générée n’en oublie aucun.
Peek lit la sortie. Elle propage une valeur déjà remplie, contrairement à Previous qui relit la source.
Trier avant de propager. ORDER BY n’existe que sur un LOAD RESIDENT : prévoyez une table temporaire et un NOCONCATENATE.
While pour les intervalles. IterNo() transforme un début et une fin en une ligne par jour.
Join sans clé commune. Il produit toutes les combinaisons ; Where Not Exists n’ajoute que celles qui manquent.

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.

// Générer les données manquantes dans Qlik Sense : script d'essai
// D'après « Generating Missing Data », 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 affichez les tables dans la visionneuse du modèle de données.
SET vExemple = 1;   // 1 à 7

IF $(vExemple) = 1 THEN
    // Exemple 1 : un calendrier principal sans trou, de la première à la dernière date
    Transactions:
    LOAD Date(Date#(Date, 'YYYY-MM-DD')) as Date, Amount
    INLINE [
    Date, Amount
    2013-01-01, 120
    2013-01-04, 80
    2013-01-08, 95
    ];

    MinMaxDate:
    LOAD Min(Date) as MinDate,
         Max(Date) as MaxDate
    RESIDENT Transactions;

    LET vMinDate = Peek('MinDate', -1, 'MinMaxDate') - 1;
    LET vMaxDate = Peek('MaxDate', -1, 'MinMaxDate');
    DROP TABLE MinMaxDate;

    MasterCalendar:
    LOAD Date,
         Year(Date)  as Year,
         Month(Date) as Month,
         Day(Date)   as Day;
    LOAD Date($(vMinDate) + RecNo()) as Date
    AUTOGENERATE $(vMaxDate) - $(vMinDate);
    // 8 dates dans MasterCalendar, dont 5 sans transaction.
END IF

IF $(vExemple) = 2 THEN
    // Exemple 2 : les taux connus aux seules dates de changement (premier schéma)
    TempRates:
    LOAD Date(Date#(Date, 'YYYY-MM-DD')) as Date, Rate
    INLINE [
    Date, Rate
    2013-01-01, 1.01
    2013-01-04, 1.08
    2013-01-08, 1.07
    ];

    MinMaxDate:
    LOAD Min(Date) as MinDate, Max(Date) as MaxDate RESIDENT TempRates;
    LET vMinDate = Peek('MinDate', -1, 'MinMaxDate') - 1;
    LET vMaxDate = Peek('MaxDate', -1, 'MinMaxDate');
    DROP TABLE MinMaxDate;

    JOIN (TempRates)
    LOAD Date($(vMinDate) + RecNo()) as Date
    AUTOGENERATE $(vMaxDate) - $(vMinDate);

    Rates:
    NOCONCATENATE
    LOAD Date,
         If(IsNull(Rate), Peek('Rate'), Rate) as Rate
    RESIDENT TempRates
    ORDER BY Date;

    DROP TABLE TempRates;
    // 8 lignes : 1.01 du 1er au 3, 1.08 du 4 au 7, 1.07 le 8.
END IF

IF $(vExemple) = 3 THEN
    // Exemple 3 : un cumul qui repart de zéro à chaque produit
    TempData:
    LOAD Product, Date(Date#(Date, 'YYYY-MM-DD')) as Date, Amount
    INLINE [
    Product, Date, Amount
    A, 2013-01-01, 10
    B, 2013-01-01, 4
    A, 2013-01-02, 5
    B, 2013-01-03, 6
    A, 2013-01-03, 7
    ];

    Data:
    NOCONCATENATE
    LOAD Product, Date, Amount,
         If(Product = Peek('Product'),
            RangeSum(Amount, Peek('AccumulatedAmount')),
            RangeSum(Amount)) as AccumulatedAmount
    RESIDENT TempData
    ORDER BY Product, Date;

    DROP TABLE TempData;
    // A : 10, 15, 22 ; B : 4, 10.
END IF

IF $(vExemple) = 4 THEN
    // Exemple 4 : une ligne par jour de validité d'un contrat (deuxième schéma)
    Policies:
    LOAD PolicyID,
         Date(Date#(BirthDate, 'YYYY-MM-DD')) as BirthDate,
         Date(Date#(FromDate, 'YYYY-MM-DD'))  as FromDate,
         Date(Date#(ToDate, 'YYYY-MM-DD'))    as ToDate
    INLINE [
    PolicyID, BirthDate, FromDate, ToDate
    P01, 1980-03-02, 2013-03-01, 2013-03-04
    ];

    Policies_x_Dates:
    LOAD PolicyID,
         IterNo() as IterNo,
         Age(FromDate + IterNo() - 1, BirthDate) as Age,
         Date(FromDate + IterNo() - 1) as ReferenceDate
    RESIDENT Policies
    WHILE IterNo() <= ToDate - FromDate + 1;
    // 4 lignes, du 1er au 4 mars ; l'âge passe de 32 à 33 ans le 2 mars.
END IF

IF $(vExemple) = 5 THEN
    // Exemple 5 : les soldes de stock complétés par produit et par jour (troisième schéma)
    // A. Soldes existants, avec une clé produit-date
    TempBalances:
    LOAD ProductID, Date, Balance,
         ProductID & '|' & Num(Date) as Product_x_DateID;
    LOAD ProductID, Date(Date#(Date, 'YYYY-MM-DD')) as Date, Balance
    INLINE [
    ProductID, Date, Balance
    A, 2013-01-01, 20
    A, 2013-01-03, 15
    B, 2013-01-02, 8
    ];

    MinMaxDate:
    LOAD Min(Date) as MinDate, Max(Date) as MaxDate RESIDENT TempBalances;
    LET vMinDate = Peek('MinDate', -1, 'MinMaxDate') - 1;
    LET vMaxDate = Peek('MaxDate', -1, 'MinMaxDate');
    DROP TABLE MinMaxDate;

    // B. Toutes les combinaisons produit-date
    TempProduct_x_Dates:
    LOAD DISTINCT ProductID RESIDENT TempBalances;
    JOIN (TempProduct_x_Dates)
    LOAD Date($(vMinDate) + RecNo()) as Date
    AUTOGENERATE $(vMaxDate) - $(vMinDate);

    // C. Ajouter seulement les combinaisons absentes
    CONCATENATE (TempBalances)
    LOAD * WHERE NOT Exists(Product_x_DateID);
    LOAD ProductID, Date,
         ProductID & '|' & Num(Date) as Product_x_DateID
    RESIDENT TempProduct_x_Dates;

    // D. Trier et reporter le dernier solde, produit par produit
    ProductBalances:
    NOCONCATENATE
    LOAD ProductID, Date,
         If(ProductID = Peek('ProductID') and IsNull(Balance),
            Peek('Balance'),
            RangeSum(Balance)) as Balance
    RESIDENT TempBalances
    ORDER BY ProductID, Date;

    // E. Nettoyer
    DROP TABLES TempProduct_x_Dates, TempBalances;
    // 6 lignes : A 20, 20, 15 ; B 0, 8, 8.
END IF

IF $(vExemple) = 6 THEN
    // Exemple 6 : SubField éclate une liste en autant de lignes
    Employees:
    LOAD * INLINE [
    Emp No|Name|Skills
    101|Alice|Qlik Sense, SQL, Python
    102|Bruno|Talend, SQL
    ] (delimiter is '|');

    Employees_x_Skills:
    LOAD [Emp No],
         Trim(SubField(Skills, ',')) as Skill
    RESIDENT Employees;
    // 5 lignes : trois compétences pour 101, deux pour 102.
END IF

IF $(vExemple) = 7 THEN
    // Exemple 7 : 100 000 lancers de deux dés (simulation de Monte-Carlo)
    Throws:
    LOAD ThrowNo, Die1, Die2, Die1 + Die2 as DiceSum;
    LOAD RecNo()         as ThrowNo,
         Ceil(Rand() * 6) as Die1,
         Ceil(Rand() * 6) as Die2
    AUTOGENERATE 100000;
    // Graphique : dimension DiceSum, mesure Count(ThrowNo) / Count(TOTAL ThrowNo).
END IF

Source

« Generating Missing Data », QlikView Technical Brief, Henric Cronström (alias « hic » sur Qlik Community), 4 février 2013. Publié sur Qlik Community, 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.