📚 Qlik Sense Kurs – Artikel 6 von 28

← Vorheriger Artikel: Daten transformieren – String, Math, Date Functions
→ Nächster Artikel: Incremental Loading – Nur Changes laden

Was kann man in 14 Minuten über CROSSTABLE und GENERIC LOAD lernen?

Daten liegen oft in einem für Menschen lesbaren, aber für Analysen ungeeigneten «breiten» Format vor (z.B. Monate als Spalten). Dieser Guide zeigt Ihnen Qliks leistungsstarke Werkzeuge, um solche Daten in ein analysefreundliches «langes» Format umzuwandeln – ein Prozess, der als Transposition oder «Unpivot» bekannt ist.

1. CROSSTABLE souverän einsetzen, um Excel-Reports mit Zeitdimensionen in den Spalten zu normalisieren.
2. GENERIC LOAD verstehen, um hochflexible EAV-Strukturen (Entity-Attribute-Value) in ein sauberes, assoziatives Modell zu überführen.
3. Die richtige Strategie für den jeweiligen Anwendungsfall wählen und Performance-Fallstricke vermeiden.

Zeitinvestition: 14 Min Lesen + 1.5 Std Hands-on
Voraussetzung: Daten transformieren verstanden
Quick Win: In 10 Minuten einen Excel-Report mit CROSSTABLE transponieren

Wie transponiere ich einen Excel-Report mit CROSSTABLE?

Das Problem: Sie erhalten einen monatlichen Verkaufsreport, bei dem jeder Monat eine eigene Spalte ist. Für Qlik ist dieses «breite» Format ineffizient, da man nicht einfach nach «Monat» filtern oder Summen über die Zeit bilden kann.

Die Lösung mit CROSSTABLE: Wir «schmelzen» die Monats-Spalten in zwei neue Spalten um: eine für den Attributnamen (den Monat) und eine für den Wert (den Umsatz).

So funktioniert’s:

1. Qualifier-Felder identifizieren: Welche Spalten sollen erhalten bleiben? In unserem Fall sind das Region und Product. Das sind 2 Qualifier-Felder.
2. Attribut- und Wert-Spalten definieren: Wir sagen Qlik, dass die Monatsnamen in eine neue Spalte namens Month und die dazugehörigen Zahlen in eine Spalte namens SalesAmount kommen sollen.
3. CROSSTABLE anwenden: Wir setzen den Befehl vor das LOAD-Statement und geben die Anzahl der Qualifier-Felder an.

SalesTransposed:
CROSSTABLE(Month, SalesAmount, 2)
LOAD
    Region,
    Product,
    Jan,
    Feb,
    Mar,
    Apr,
    Mai,
    Jun
FROM [lib://DataFiles/monthly_sales.xlsx]
(ooxml, embedded labels, table is Sheet1);

Erklärung der Parameter:

• CROSSTABLE(Month, SalesAmount, 2):
  • Month: Der Name der neuen Attribut-Spalte (enthält die alten Spaltennamen ‚Jan‘, ‚Feb‘, etc.).
  • SalesAmount: Der Name der neuen Wert-Spalte (enthält die Zellwerte).
  • 2: Die Anzahl der führenden Spalten (Region, Product), die nicht transponiert werden sollen. Dies ist der wichtigste Parameter!

Checkpoint: Sehen Sie in Ihrem Datenmodell eine Tabelle mit den vier Spalten `Region`, `Product`, `Month` und `SalesAmount`? Perfekt!

Wann welche Transpositions-Methode?

Die Wahl des Werkzeugs hängt von der Struktur Ihrer Quelldaten ab.

Wie kann man CROSSTABLE für Excel-Reports normalisieren?

CROSSTABLE ist Ihr Standardwerkzeug, um denormalisierte Berichte in eine saubere, für Qlik optimierte Struktur zu überführen. Das Ziel ist es, aus Spalten, die eigentlich Datenwerte sind (wie Monate, Jahre, Kennzahlen), echte Datenzeilen zu machen.

Nach einer CROSSTABLE-Transformation ist oft ein zweiter RESIDENT-Load notwendig, um die neu erstellten Felder weiter zu verarbeiten (z.B. aus dem Text «Jan_2024» ein echtes Datum zu machen).

MonthlySalesTransposed:
CROSSTABLE(MonthYear, Amount, 2)
LOAD
    Region,
    Product,
    Jan_2024,
    Feb_2024,
    Mar_2024
FROM [lib://DataFiles/monthly_sales_report.xlsx];

CleanMonthlySales:
LOAD
    Region,
    Product,
    Date(Date#(MonthYear, 'MMM_YYYY'), 'YYYY-MM') as SalesMonth,
    If(IsNum(Amount), Amount, 0) as CleanAmount
RESIDENT MonthlySalesTransposed;

DROP TABLE MonthlySalesTransposed;

Wie funktioniert GENERIC LOAD von EAV zu optimierten Tabellen?

Was ist eine EAV-Struktur?

EAV steht für Entity-Attribute-Value. Es ist eine sehr flexible Art, Daten zu speichern, bei denen die Attribute pro Entität variieren können.

• Entity: Das Objekt, das beschrieben wird (z.B. eine Kunden-ID ‚C001‘).
• Attribute: Eine Eigenschaft des Objekts (z.B. ‚Name‘, ‚Region‘, ‚Umsatz‘).
• Value: Der Wert der Eigenschaft (z.B. ‚Max Mustermann‘, ‚Nord‘, ‚15000‘).

Eine EAV-Tabelle sieht typischerweise so aus:

CustomerID | AttributeName | AttributeValue
C001       | Name          | Max Mustermann
C001       | Region        | Nord
C002       | Name          | Anna Schmidt
C002       | Umsatz        | 25000

Wie funktioniert GENERIC LOAD im Vergleich zu CROSSTABLE?

GENERIC LOAD liest eine solche EAV-Struktur und erstellt daraus automatisch für jedes einzigartige Attribut eine eigene, separate Tabelle. Diese Tabellen werden über das Schlüsselfeld (die Entity) miteinander verknüpft. Das Ergebnis ist ein perfektes Star Schema!

GENERIC LOAD
    CustomerID,
    AttributeName,
    AttributeValue
FROM customer_attributes.csv;

Aus der obigen EAV-Tabelle würde Qlik automatisch diese beiden Tabellen erstellen, die über CustomerID verknüpft sind:

• Tabelle «Name»: Enthält die Felder `CustomerID` und `Name`.
• Tabelle «Region»: Enthält die Felder `CustomerID` und `Region`.
• Tabelle «Umsatz»: Enthält die Felder `CustomerID` und `Umsatz`.

Achtung: Seien Sie vorsichtig, wenn Ihre EAV-Tabelle sehr viele verschiedene Attribute enthält. GENERIC würde für jedes eine Tabelle erstellen, was Ihr Datenmodell unübersichtlich machen kann. Filtern Sie die EAV-Tabelle am besten vorab auf die Attribute, die Sie wirklich benötigen.

Was ist der Unterschied zwischen manueller und automatischer Transposition?

Wie verwendet man einen manuellen Pivot mit `CONCATENATE`?

Manchmal ist CROSSTABLE nicht flexibel genug. Wenn Sie zum Beispiel pro Monat zwei Wertespalten haben (z.B. `Q1_Umsatz` und `Q1_Menge`), müssen Sie die Transposition manuell durchführen. Dies geschieht, indem Sie die Daten für jedes Quartal einzeln laden und mit `CONCATENATE` untereinander hängen.

ManualPivot:
LOAD ProductID, 'Q1' as Quarter, Q1_Sales as Sales, Q1_Units as Units
FROM quarterly_data.csv;

CONCATENATE (ManualPivot)
LOAD ProductID, 'Q2' as Quarter, Q2_Sales as Sales, Q2_Units as Units
FROM quarterly_data.csv;

CONCATENATE (ManualPivot)
LOAD ProductID, 'Q3' as Quarter, Q3_Sales as Sales, Q3_Units as Units
FROM quarterly_data.csv;

Wie funktioniert die Pivotierung auf Diagramm-Ebene?

Bevor Sie Ihr Skript komplex machen, fragen Sie sich: Muss die Transposition wirklich im Skript stattfinden? Wenn Ihre Daten bereits sauber strukturiert sind (z.B. eine Spalte für Datum, eine für Umsatz), können Sie die Pivotierung oft direkt im Frontend in einem Pivot-Tabellen-Objekt vornehmen. Dies hält Ihr Skript schlank und bietet dem Benutzer maximale Flexibilität.

Wie behebe ich Probleme beim Transponieren von Daten – CROSSTABLE vs GENERIC LOAD?

Warum liefert CROSSTABLE unerwartete Ergebnisse?

Problem: Nach der Transposition fehlen Spalten oder es werden zu viele Zeilen erzeugt.

Ursache: Dies liegt fast immer an einem falsch gesetzten Qualifier-Parameter (die Zahl `N`). Zählen Sie exakt die Anzahl der Spalten von links, die als feste Spalten erhalten bleiben sollen. Wenn dieser Wert falsch ist, versucht Qlik, die falschen Spalten zu transponieren.

Lösung: Überprüfen Sie die Anzahl der Qualifier-Felder in Ihrem LOAD-Statement und passen Sie die Zahl im CROSSTABLE-Befehl an.

Wie erzeugt GENERIC LOAD zu viele Tabellen?

Problem: Das Skript wird langsam und das Datenmodell ist unübersichtlich mit hunderten von Tabellen.

Ursache: Ihre EAV-Quelldatei enthält sehr viele verschiedene, möglicherweise irrelevante Attribute.

Lösung: Führen Sie einen `RESIDENT`-Load vor dem GENERIC-Load durch und filtern Sie mit einer `WHERE`-Klausel nur die Attribute, die Sie für Ihr Modell benötigen. Zum Beispiel: `WHERE WildMatch(AttributeName, ‚*Umsatz*‘, ‚*Region*‘, ‚*Status*‘)`.

Was sind die nächsten Schritte im Kurs zu CROSSTABLE vs GENERIC LOAD?

Als nächstes: Incremental Loading – Nur Changes laden

Verwandte Themen:

• Daten transformieren – String, Math, Date Functions
• Mapping Tables vs JOINs – Performance-Optimierung
• Incremental Loading – Nur Changes laden