Crosstable
Das Ladepräfix crosstable wird verwendet, um als „Kreuztabelle“ oder „Pivottabelle“ strukturierte Daten umzuwandeln. Derart strukturierte Daten liegen in der Regel vor, wenn mit Arbeitsblattquellen gearbeitet wird. Die Ausgabe und das Ziel des Ladepräfixes crosstable bestehen darin, diese Strukturen in eine entsprechende reguläre spaltenbasierte Tabelle umzuwandeln, da sich diese Struktur meist besser für die Analyse in Qlik Sense eignet.
Syntax:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
Argument | Beschreibung |
---|---|
attribute field name | Der gewünschte Ausgabefeldname, der die horizontal orientierte Dimension beschreibt, die umgewandelt werden soll (die Kopfzeile). |
data field name |
Der gewünschte Ausgabefeldname, der die horizontal orientierten Daten der Dimension beschreibt, die umgewandelt werden sollen (die Matrix der Datenwerte unter der Kopfzeile). |
n |
Die Anzahl der Zusatzfelder bzw. unveränderten Dimensionen, die der Tabelle vorangestellt sind und in die generische Form umgewandelt werden sollen. Der Standardwert ist 1. |
Diese Skriptfunktion hängt mit folgenden Funktionen zusammen:
Funktion | Interaktion |
---|---|
Generic | Ein Umwandlungs-Ladezusatz, der einen Datensatz mit Entität-Attribut-Wert-Struktur (EAV) in eine reguläre relationale Tabellenstruktur umwandelt und jedes gefundene Attribut als ein eigenes neues Datenfeld bzw. neue Datenspalte abtrennt. |
Beispiel 1 – Umwandeln von pivotierten Umsatzdaten (einfach)
Übersicht
Öffnen Sie den Dateneditor und fügen Sie das erste Ladeskript unten in eine neue Registerkarte ein.
Das erste Ladeskript enthält einen Datensatz, auf den später der Skriptzusatz crosstable angewendet wird, wobei der Abschnitt, der crosstable anwendet, auskommentiert ist. Das bedeutet, dass die Kommentarsyntax verwendet wurde, um diesen Abschnitt im Ladeskript zu deaktivieren.
Das zweite Ladeskript entspricht dem ersten, aber mit unkommentierter Anwendung von crosstable (durch Entfernen der Kommentarsyntax). Die Skripte werden auf diese Weise angezeigt, um den Wert der Skripterstellungsfunktion beim Umwandeln von Daten hervorzuheben.
Erstes Ladeskript (Funktion nicht angewendet)
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;
Weitere Informationen über Inline-Ladevorgänge finden Sie unter Inline-Ladevorgänge.
Ergebnisse
Laden Sie die Daten und öffnen Sie ein Arbeitsblatt. Erstellen Sie eine neue Tabelle und fügen Sie die folgenden Felder als Dimensionen hinzu:
-
Product
-
Jan 2021
-
Feb 2021
-
Mar 2021
-
Apr 2021
-
May 2021
-
Jun 2021
Produkt | Jan 2021 | Feb 2021 | Mär 2021 | Apr 2021 | Mai 2021 | Jun 2021 |
---|---|---|---|---|---|---|
A | 100 | 98 | 103 | 63 | 108 | 82 |
B | 284 | 279 | 297 | 305 | 294 | 292 |
C | 50 | 53 | 50 | 54 | 49 | 51 |
Dieses Skript ermöglicht die Erstellung einer Kreuztabelle mit einer Spalte für jeden Monat und einer Zeile pro Produkt. Im aktuellen Format ist eine Analyse der Daten schwierig. Es wäre wesentlich praktischer, in einer dreispaltigen Tabelle alle Zahlen in einem Feld und alle Monate in einem anderen zu haben. Im nächsten Abschnitt wird erläutert, wie Sie diese Umwandlung der Kreuztabelle vornehmen.
Zweites Ladeskript (Funktion angewendet)
Heben Sie die Kommentierung des Skripts auf, indem Sie die // entfernen. Das Ladeskript sollte folgendermaßen aussehen:
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;
Weitere Informationen über Inline-Ladevorgänge finden Sie unter Inline-Ladevorgänge.
Ergebnisse
Laden Sie die Daten und öffnen Sie ein Arbeitsblatt. Erstellen Sie eine neue Tabelle und fügen Sie die folgenden Felder als Dimensionen hinzu:
-
Product
-
Month
-
Sales
Produkt | Monat | Verkauf |
---|---|---|
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 |
Nachdem der Skriptzusatz angewendet wurde, ist die Kreuztabelle in ein Tabellendiagramm mit einer Spalte für Month und einer Spalte für Sales umgewandelt. Das verbessert die Lesbarkeit der Daten.
Beispiel 2 – Umwandeln von pivotierten Umsatzzieldaten in eine vertikale Tabellenstruktur (mittel)
Übersicht
Öffnen Sie den Dateneditor und fügen Sie das Ladeskript unten in eine neue Registerkarte ein.
Das Ladeskript umfasst:
-
Ein Datensatz, der in eine Tabelle namens „Targets“ geladen wird.
-
Der Ladezusatz crosstable, der die pivotierten Vertriebsmitarbeiternamen in ein eigenes Feld mit der Bezeichnung Sales Person umwandelt.
-
Die zugehörigen Umsatzzieldaten, die in einem Feld namens Target strukturiert werden.
Ladeskript
SalesTargets:
CROSSTABLE([Sales Person],Target,1)
LOAD
*
INLINE [
Area, Lisa, James, Sharon
APAC, 1500, 1750, 1850
EMEA, 1350, 950, 2050
NA, 1800, 1200, 1350
];
Ergebnisse
Laden Sie die Daten und öffnen Sie ein Arbeitsblatt. Erstellen Sie eine neue Tabelle und fügen Sie die folgenden Felder als Dimensionen hinzu:
-
Area
-
Sales Person
Fügen Sie folgende Kennzahl hinzu:
=Sum(Target)
Fläche | Vertriebsmitarbeiter | =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 |
Wenn Sie die Anzeige der Daten als pivotierte Eingabetabelle replizieren möchten, können Sie eine entsprechende Pivottabelle in einem Arbeitsblatt erstellen.
Gehen Sie folgendermaßen vor:
- Kopieren Sie die soeben erstellte Tabelle und fügen Sie sie in ein Arbeitsblatt ein.
- Ziehen Sie das Diagrammobjekt Pivottabelle auf die neu erstellte Tabellenkopie. Wählen Sie Konvertieren aus.
- Klicken Sie auf Bearbeitung fertig.
- Ziehen Sie das Feld Sales Person von der Ablage der vertikalen Spalte zur Ablage der horizontalen Spalte.
Die folgende Tabelle zeigt die Daten in ihrer anfänglichen Tabellenform, so wie sie in Qlik Sense angezeigt werden:
Fläche | Vertriebsmitarbeiter | =Sum(Target) |
---|---|---|
Summen | - | 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 |
Die entsprechende Pivottabelle gleicht der folgenden, wobei die Spalte für den Namen der einzelnen Vertriebsmitarbeiter in der längeren Zeile für Sales Person enthalten ist:
Fläche | James | Lisa | Sharon |
---|---|---|---|
APAC | 1750 | 1500 | 1850 |
EMEA | 950 | 1350 | 2050 |
NA | 1350 | 1350 | 1350 |
Beispiel 3 – Umwandeln von pivotierten Umsatz- und Zieldaten in eine vertikale Tabellenstruktur (fortgeschritten)
Übersicht
Öffnen Sie den Dateneditor und fügen Sie das Ladeskript unten in eine neue Registerkarte ein.
Das Ladeskript umfasst:
-
Ein Datensatz, der Umsatz- und Zieldaten darstellt, organisiert nach Gebiet und Monat des Jahres. Er wird in eine Tabelle namens „SalesAndTargets“ geladen.
-
Der Ladezusatz crosstable. Er wird verwendet, um die Pivotierung der Dimension Month Year aufzuheben und sie in ein dediziertes Feld zu verwandeln und um die Matrix der Umsatz- und Zielbeträge in ein dediziertes Feld namens Amount umzuwandeln.
-
Eine Konvertierung des Felds Month Year zu einem korrekten Datum anhand der Text-zu-Datum-Konvertierungsfunktion date#. Dieses datumskonvertierte Feld Month Year wird wieder mit der Tabelle SalesAndTarget über einen Ladezusatz Join verknüpft.
Ladeskript
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;
Ergebnisse
Laden Sie die Daten und öffnen Sie ein Arbeitsblatt. Erstellen Sie eine neue Tabelle und fügen Sie die folgenden Felder als Dimensionen hinzu:
-
Area
-
Month Year
Erstellen Sie die folgende Kennzahl mit der Bezeichnung Actual:
=Sum({<Type={'Actual'}>} Amount)
Erstellen Sie zudem die folgende Kennzahl mit der Bezeichnung Target:
=Sum({<Type={'Target'}>} Amount)
Fläche | Monat-Jahr | Ist | Ziel |
---|---|---|---|
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 |
Wenn Sie die Anzeige der Daten als pivotierte Eingabetabelle replizieren möchten, können Sie eine entsprechende Pivottabelle in einem Arbeitsblatt erstellen.
Gehen Sie folgendermaßen vor:
- Kopieren Sie die soeben erstellte Tabelle und fügen Sie sie in ein Arbeitsblatt ein.
- Ziehen Sie das Diagrammobjekt Pivottabelle auf die neu erstellte Tabellenkopie. Wählen Sie Konvertieren aus.
- Klicken Sie auf Bearbeitung fertig.
- Ziehen Sie das Feld Month Year von der Ablage der vertikalen Spalte zur Ablage der horizontalen Spalte.
- Ziehen Sie das Element Values von der Ablage der horizontalen Spalte zur Ablage der vertikalen Spalte.
Die folgende Tabelle zeigt die Daten in ihrer anfänglichen Tabellenform, so wie sie in Qlik Sense angezeigt werden:
Fläche | Monat-Jahr | Ist | Ziel |
---|---|---|---|
Summen | - | 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 |
Die entsprechende Pivottabelle gleicht der Folgenden, wobei die Spalte für die einzelnen Monate des Jahres in der längeren Zeile für Month Year enthalten ist:
Gebiet (Werte) | 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 – Ist | 435 | 434 | 397 | 404 | 458 | 447 | 413 | 458 | 385 | 421 | 448 | 397 |
APAC –Ziel | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 |
EMEA – Ist | 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 – Ziel | 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 – Ist | 378 | 415 | 363 | 356 | 403 | 343 | 401 | 365 | 393 | 340 | 360 | 405 |
NA – Ziel | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 |