Crosstable
Le préfixe de chargement crosstable est utilisé pour transposer des données structurées de « tableau croisé » ou de « tableau croisé dynamique ». Les données structurées de cette manière sont fréquentes lorsque vous travaillez avec des sources de feuilles de calcul. Le résultat et l'objectif du préfixe de chargement crosstable sont de transposer ces structures dans un équivalent de tableau avec des colonnes standard, car cette structure est généralement mieux adaptée à l'analyse dans Qlik Sense.
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
Argument | Description |
---|---|
attribute field name | Nom de champ de sortie souhaité décrivant la dimension orientée à l'horizontale à transposer (la ligne d'en-tête). |
data field name |
Nom de champ de sortie souhaité décrivant les données orientées à l'horizontale de la dimension à transposer (la matrice des valeurs de données sous la ligne d'en-tête). |
n |
Nombre de champs du qualificateur ou dimensions inchangées précédant le tableau à transformer au format générique. La valeur par défaut est 1. |
Cette fonction de script est liée aux fonctions suivantes :
Fonction | Interaction |
---|---|
Generic | Préfixe de chargement transformation qui prend un ensemble de données structuré au format entité-attribut-valeur et le transforme en une structure de table relationnelle standard, séparant chaque attribut rencontré dans un nouveau champ ou dans une nouvelle colonne de données. |
Exemple 1 – Transformation de données sales pivotées (simple)
Vue d'ensemble
Ouvrez l'éditeur de chargement de données et ajoutez le premier script de chargement ci-dessous à un nouvel onglet.
Le premier script de chargement contient un ensemble de données auquel le préfixe de script crosstable sera appliqué ultérieurement, la section appliquant crosstable commenté. Cela signifie que la syntaxe de commentaire a été utilisée pour désactiver cette section dans le script de chargement.
Le deuxième script de chargement est le même que le premier, mais avec l'application de crosstable non commenté (activé via la suppression de la syntaxe de commentaire). Les scripts sont présentés de cette manière pour mettre en évidence la valeur de cette fonction de script dans la transformation des données.
Premier script de chargement (fonction non appliquée)
tmpData:
//Crosstable (MonthText, Sales)
Load * inline [
Product, Jan 2021, Feb 2021, Mar 2021, Apr 2021, May 2021, Jun 2021
A, 100, 98, 103, 63, 108, 82
B, 284, 279, 297, 305, 294, 292
C, 50, 53, 50, 54, 49, 51];
//Final:
//Load Product,
//Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,
//Sales
//Resident tmpData;
//Drop Table tmpData;
Pour plus d'informations sur l'utilisation des chargements inline, voir Chargements inline.
Résultats
Chargez les données et ouvrez une feuille. Créez une table et ajoutez ces champs comme dimensions :
-
Product
-
Jan 2021
-
Feb 2021
-
Mar 2021
-
Apr 2021
-
May 2021
-
Jun 2021
Product | Jan 2021 | Feb 2021 | Mar 2021 | Apr 2021 | May 2021 | Jun 2021 |
---|---|---|---|---|---|---|
A | 100 | 98 | 103 | 63 | 108 | 82 |
B | 284 | 279 | 297 | 305 | 294 | 292 |
C | 50 | 53 | 50 | 54 | 49 | 51 |
Ce script permet la création d'un tableau croisé (crosstable) avec une colonne pour chaque mois et une ligne par produit. Dans ce format, ces données ne sont pas faciles à analyser. Il serait préférable d'avoir tous les nombres dans un champ et tous les mois dans un autre, à savoir, dans un tableau à trois colonnes. La section suivante explique comment effectuer cette transformation en tableau croisé (crosstable).
Deuxième script de chargement (fonction appliquée)
Décommentez le script en supprimant //. Le script de chargement devrait ressembler au suivant :
tmpData:
Crosstable (MonthText, Sales)
Load * inline [
Product, Jan 2021, Feb 2021, Mar 2021, Apr 2021, May 2021, Jun 2021
A, 100, 98, 103, 63, 108, 82
B, 284, 279, 297, 305, 294, 292
C, 50, 53, 50, 54, 49, 51];
Final:
Load Product,
Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,
Sales
Resident tmpData;
Drop Table tmpData;
Pour plus d'informations sur l'utilisation des chargements inline, voir Chargements inline.
Résultats
Chargez les données et ouvrez une feuille. Créez une table et ajoutez ces champs comme dimensions :
-
Product
-
Month
-
Sales
Product | Mois | Sales |
---|---|---|
A | Jan 2021 | 100 |
A | Feb 2021 | 98 |
A | Mar 2021 | 103 |
A | Apr 2021 | 63 |
A | May 2021 | 108 |
A | Jun 2021 | 82 |
B | Jan 2021 | 284 |
B | Feb 2021 | 279 |
B | Mar 2021 | 297 |
B | Apr 2021 | 305 |
B | May 2021 | 294 |
B | Jun 2021 | 292 |
C | Jan 2021 | 50 |
C | Feb 2021 | 53 |
C | Mar 2021 | 50 |
C | Apr 2021 | 54 |
C | May 2021 | 49 |
C | Jun 2021 | 51 |
Une fois le préfixe de script appliqué, le tableau croisé (crosstable) est transformé en tableau simple avec une colonne pour Month et une autre pour Sales. Cela améliore la lisibilité des données.
Exemple 2 - Transformation de données sales target pivotées en une structure de tableau verticale (intermédiaire)
Vue d'ensemble
Ouvrez l'éditeur de chargement de données et ajoutez le script de chargement ci-dessous à un nouvel onglet.
Le script de chargement contient :
-
Ensemble de données chargé dans une table appelée Targets.
-
Préfixe de chargement crosstable, qui transpose les noms sales person pivotés dans un champ qui lui est propre, intitulé Sales Person.
-
Données sales target associées, structurées dans un champ nommé Target.
Script de chargement
SalesTargets:
CROSSTABLE([Sales Person],Target,1)
LOAD
*
INLINE [
Area, Lisa, James, Sharon
APAC, 1500, 1750, 1850
EMEA, 1350, 950, 2050
NA, 1800, 1200, 1350
];
Résultats
Chargez les données et ouvrez une feuille. Créez une table et ajoutez ces champs comme dimensions :
-
Area
-
Sales Person
Ajoutez cette mesure :
=Sum(Target)
Area | Sales Person | =Sum(Target) |
---|---|---|
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
NA | James | 1200 |
NA | Lisa | 1800 |
NA | Sharon | 1350 |
Si vous souhaitez répliquer l'affichage des données sous forme de tableau d'entrée pivoté, vous pouvez créer un tableau croisé dynamique équivalent dans une feuille.
Procédez comme suit :
- Copiez et collez le tableau que vous venez de créer dans la feuille.
- Faites glisser l'objet graphique Tableau croisé dynamique par-dessus la copie de tableau que vous venez de créer. Sélectionnez Convertir.
- Cliquez sur Édition terminée.
- Faites glisser le champ Sales Person de la partie supérieure de la colonne verticale vers la partie supérieure de la colonne horizontale.
Le tableau suivant montre les données sous leur forme de tableau initiale, telles qu'affichées dans Qlik Sense :
Area | Sales Person | =Sum(Target) |
---|---|---|
Totals | - | 13800 |
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
NA | James | 1200 |
NA | Lisa | 1800 |
NA | Sharon | 1350 |
Le tableau croisé dynamique équivalent ressemble à ce qui suit, la colonne du nom de chaque vendeur étant contenue dans la ligne plus grande pour Sales Person :
Area | James | Lisa | Sharon |
---|---|---|---|
APAC | 1750 | 1500 | 1850 |
EMEA | 950 | 1350 | 2050 |
NA | 1350 | 1350 | 1350 |
Exemple 3 - Transformation de données sales et target pivotées en une structure de tableau verticale (avancé)
Vue d'ensemble
Ouvrez l'éditeur de chargement de données et ajoutez le script de chargement ci-dessous à un nouvel onglet.
Le script de chargement contient :
-
Ensemble de données représentant les données sales et targets, organisées par secteur et mois de l'année. Cet ensemble de données est chargé dans une table appelée SalesAndTargets.
-
Préfixe de chargement crosstable. Ceci est utilisé pour annuler le pivotement de la dimension Month Year dans un champ dédié, ainsi que pour transposer la matrice des montants sales et target dans un champ dédié appelé Amount.
-
Conversion du champ Month Year du format texte en une date appropriée, à l'aide de la fonction de conversion de texte en date date#. Ce champ Month Year converti en date est de nouveau joint à la table SalesAndTarget via un préfixe de chargement Join.
Script de chargement
SalesAndTargets:
CROSSTABLE(MonthYearAsText,Amount,2)
LOAD
*
INLINE [
Area Type Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22
APAC Target 425 425 425 425 425 425 425 425 425 425 425 425
APAC Actual 435 434 397 404 458 447 413 458 385 421 448 397
EMEA Target 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5 362.5
EMEA Actual 363.5 359.5 337.5 361.5 341.5 337.5 379.5 352.5 327.5 337.5 360.5 334.5
NA Target 375 375 375 375 375 375 375 375 375 375 375 375
NA Actual 378 415 363 356 403 343 401 365 393 340 360 405
] (delimiter is '\t');
tmp:
LOAD DISTINCT MonthYearAsText,date#(MonthYearAsText,'MMM-YY') AS [Month Year]
RESIDENT SalesAndTargets;
JOIN (SalesAndTargets)
LOAD * RESIDENT tmp;
DROP TABLE tmp;
DROP FIELD MonthYearAsText;
Résultats
Chargez les données et ouvrez une feuille. Créez une table et ajoutez ces champs comme dimensions :
-
Area
-
Month Year
Créez la mesure suivante, avec le libellé Actual :
=Sum({<Type={'Actual'}>} Amount)
Créez également la mesure suivante, avec le libellé Target :
=Sum({<Type={'Target'}>} Amount)
Area | Month Year | Actual | Target |
---|---|---|---|
APAC | Jan-22 | 435 | 425 |
APAC | Feb-22 | 434 | 425 |
APAC |
Mar-22 | 397 | 425 |
APAC | Apr-22 | 404 | 425 |
APAC | May-22 | 458 | 425 |
APAC | Jun-22 | 447 | 425 |
APAC | Jul-22 | 413 | 425 |
APAC | Aug-22 | 458 | 425 |
APAC | Sep-22 | 385 | 425 |
APAC | Oct-22 | 421 | 425 |
APAC | Nov-22 | 448 | 425 |
APAC | Dec-22 | 397 | 425 |
EMEA | Jan-22 | 363.5 | 362.5 |
EMEA | Feb-22 | 359.5 | 362.5 |
Si vous souhaitez répliquer l'affichage des données sous forme de tableau d'entrée pivoté, vous pouvez créer un tableau croisé dynamique équivalent dans une feuille.
Procédez comme suit :
- Copiez et collez le tableau que vous venez de créer dans la feuille.
- Faites glisser l'objet graphique Tableau croisé dynamique par-dessus la copie de tableau que vous venez de créer. Sélectionnez Convertir.
- Cliquez sur Édition terminée.
- Faites glisser le champ Month Year de la partie supérieure de la colonne verticale vers la partie supérieure de la colonne horizontale.
- Faites glisser l'élément Values de la partie supérieure de la colonne horizontale vers la partie supérieure de la colonne verticale.
Le tableau suivant montre les données sous leur forme de tableau initiale, telles qu'affichées dans Qlik Sense :
Area | Month Year | Actual | Target |
---|---|---|---|
Totals | - | 13812 | 13950 |
APAC | Jan-22 | 435 | 425 |
APAC | Feb-22 | 434 | 425 |
APAC |
Mar-22 | 397 | 425 |
APAC | Apr-22 | 404 | 425 |
APAC | May-22 | 458 | 425 |
APAC | Jun-22 | 447 | 425 |
APAC | Jul-22 | 413 | 425 |
APAC | Aug-22 | 458 | 425 |
APAC | Sep-22 | 385 | 425 |
APAC | Oct-22 | 421 | 425 |
APAC | Nov-22 | 448 | 425 |
APAC | Dec-22 | 397 | 425 |
EMEA | Jan-22 | 363.5 | 362.5 |
EMEA | Feb-22 | 359.5 | 362.5 |
Le tableau croisé dynamique équivalent ressemble à ce qui suit, la colonne de chaque mois de l'année étant contenue dans la ligne plus grande pour Month Year :
Area (Values) | Jan-22 | Feb-22 | Mar-22 | Apr-22 | May-22 | Jun-22 | Jul-22 | Aug-22 | Sep-22 | Oct-22 | Nov-22 | Dec-22 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
APAC - Actual | 435 | 434 | 397 | 404 | 458 | 447 | 413 | 458 | 385 | 421 | 448 | 397 |
APAC - Target | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 |
EMEA - Actual | 363.5 | 359.5 | 337.5 | 361.5 | 341.5 | 337.5 | 379.5 | 352.5 | 327.5 | 337.5 | 360.5 | 334.5 |
EMEA - Target | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 |
NA - Actual | 378 | 415 | 363 | 356 | 403 | 343 | 401 | 365 | 393 | 340 | 360 | 405 |
NA - Target | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 |