UNION et UNION ALL dans Qlik Sense

Où sont passés UNION et UNION ALL ?

Quand on vient du SQL, UNION et UNION ALL sont des réflexes naturels pour réunir plusieurs jeux de données. Dans Qlik Sense, il n’existe pas une traduction unique : tout dépend de ce que l’on veut réunir et du moment où l’opération doit avoir lieu.

UNION ou UNION ALL en SQL ?

Prenons deux petits jeux de ventes fictifs SOSQLIK.

Commande  Client  Montant
C001      Alice      120
C002      Bruno      180

Commande  Client  Montant
C002      Bruno      180
C003      Chloé      250

Avec UNION, les lignes identiques du résultat sont éliminées. Avec UNION ALL, elles sont conservées.

SELECT * FROM VENTES_NORD
UNION
SELECT * FROM VENTES_SUD;

SELECT * FROM VENTES_NORD
UNION ALL
SELECT * FROM VENTES_SUD;

Concatenate : empiler les lignes

Dans le script Qlik, Concatenate ajoute les enregistrements d’un chargement à une table existante. Les lignes identiques ne sont pas supprimées automatiquement.

VENTES:
LOAD * INLINE [
Commande, Client, Montant
C001, Alice, 120
C002, Bruno, 180
];

Concatenate (VENTES)
LOAD * INLINE [
Commande, Client, Montant
C002, Bruno, 180
C003, Chloé, 250
];

Comment supprimer les doublons dans le script Qlik ?

Après les deux chargements précédents, VENTES_TMP contient quatre lignes : C001, deux fois C002, puis C003. Pour retrouver un comportement proche d’un UNION SQL, il faut donc créer une nouvelle table en demandant explicitement à Qlik de ne conserver qu’une seule occurrence des lignes strictement identiques.

VENTES_TMP:
LOAD * INLINE [
Commande, Client, Montant
C001, Alice, 120
C002, Bruno, 180
];

Concatenate (VENTES_TMP)
LOAD * INLINE [
Commande, Client, Montant
C002, Bruno, 180
C003, Chloé, 250
];

VENTES:
NoConcatenate
LOAD DISTINCT *
Resident VENTES_TMP;

DROP TABLE VENTES_TMP;

Étape 1 — construire une table temporaire

Les deux premiers LOAD alimentent VENTES_TMP. Le Concatenate empile simplement les lignes : à ce stade, le doublon C002 / Bruno / 180 est volontairement encore présent.

Étape 2 — relire la table avec DISTINCT

Resident VENTES_TMP demande à Qlik de relire une table déjà chargée en mémoire. Le mot-clé DISTINCT s’applique au résultat de ce nouveau LOAD et élimine les lignes identiques sur l’ensemble des champs chargés.

Dans notre exemple, les deux lignes C002, Bruno, 180 sont identiques sur les trois champs. Une seule est donc conservée.

Étape 3 — pourquoi NoConcatenate ?

La nouvelle table VENTES possède exactement les mêmes champs que VENTES_TMP. Qlik pourrait donc les concaténer automatiquement. NoConcatenate lui demande au contraire de créer une table distincte afin que nous disposions réellement du résultat dédupliqué dans VENTES.

Étape 4 — supprimer la table temporaire

Une fois VENTES construite, VENTES_TMP n’a plus d’utilité. DROP TABLE VENTES_TMP; la retire du modèle et évite de conserver deux tables contenant les mêmes données.

On peut aussi laisser Oracle faire l’UNION

Lorsque Qlik charge les données depuis Oracle, UNION et UNION ALL peuvent rester dans le SQL SELECT. Dans ce cas, Oracle réalise l’opération et Qlik charge le résultat renvoyé.

LIB CONNECT TO 'ORACLE_PROD';

VENTES:
LOAD
    COMMANDE,
    CLIENT,
    MONTANT;

SQL SELECT
    COMMANDE,
    CLIENT,
    MONTANT
FROM VENTES_MAGASIN

UNION

SELECT
    COMMANDE,
    CLIENT,
    MONTANT
FROM VENTES_WEB;

Pour conserver toutes les lignes, il suffit ici de remplacer UNION par UNION ALL.

Dans les ensembles, UNION devient simplement +

Nous changeons maintenant complètement de niveau. Il ne s’agit plus d’empiler des lignes ou de construire le modèle : le Set Analysis combine des ensembles utilisés par une expression.

Sum({
    1<Pays={'France'}>
    +
    1<Pays={'Espagne'}>
} Montant)

L’opérateur + réalise l’union des deux ensembles. Un enregistrement appartenant aux deux ensembles n’est pas « ajouté une seconde fois » : nous manipulons ici des ensembles, pas une pile de lignes.

Les quatre opérateurs d’ensembles

Une fois l’union comprise, les autres opérateurs du Set Analysis deviennent beaucoup plus faciles à mémoriser. Prenons A = clients ayant acheté une formation Qlik et B = clients ayant acheté une formation SQL.

Ces opérateurs sont détaillés avec des exemples complémentaires dans le tutoriel Set Analysis SOSQLIK →

Quel mécanisme choisir ?