Crosstable
Prefiks ładowania crosstable służy do transpozycji danych strukturalnych typu „tabela krzyżowa” lub „tabela przestawna”. Dane o takiej strukturze są często spotykane podczas pracy ze źródłami w postaci arkuszy kalkulacyjnych. Wynikiem i celem prefiksu ładowania crosstable jest transpozycja takich struktur do zwykłego odpowiednika tabeli z kolumnami, ponieważ ta struktura jest ogólnie lepiej przystosowana do analizy w Qlik Sense.
Składnia:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
Argument | Opis |
---|---|
attribute field name | Nazwa pola żądanych danych wyjściowych opisująca wymiar zorientowany poziomo, który ma być transponowany (wiersz nagłówka). |
data field name |
Nazwa pola żądanych danych wyjściowych opisująca wymiar zorientowany poziomo, który ma być transponowany (macierz wartości danych pod wierszem nagłówka). |
n |
Liczba poprzedzających tabelę pól kwalifikatora, czyli niezmienionych wymiarów, które zostaną przekształcone do postaci ogólnej. Wartością domyślną jest 1. |
Ta funkcja skryptu jest powiązana z następującymi funkcjami:
Funkcja | Interakcja |
---|---|
Generic | Prefiks ładowania transformacji, który powoduje pobranie uporządkowanego zestawu danych jednostka-atrybut-wartość i przekształcenie go w zwykłą relacyjną strukturę tabeli, oddzielając każdy napotkany atrybut w nowym polu lub kolumnie danych. |
Przykład 1 – Przekształcenie przestawnych danych sprzedaży (proste)
Przegląd
Otwórz Edytor ładowania danych i dodaj pierwszy skrypt ładowania poniżej do nowej karty.
Pierwszy skrypt ładowania zawiera zestaw danych, do którego prefiks skryptu crosstable zostanie zastosowany później, a sekcja dotycząca zastosowania crosstable została wykomentowana. Oznacza to, że do wyłączenia tej sekcji w skrypcie ładowania użyto składni komentarza.
Drugi skrypt ładowania jest taki sam jak pierwszy, ale z zastosowaniem crosstable bez wykomentowania (włączone przez usunięcie składni komentarza). Skrypty są pokazane w ten sposób, aby podkreślić wartość tej funkcji skryptowej w przekształcaniu danych.
Pierwszy skrypt ładowania (funkcja nie jest stosowana)
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;
Aby uzyskać więcej informacji o ładowaniu wbudowanym, zobacz temat Ładowania wbudowane.
Wyniki
Załaduj dane i otwórz arkusz. Utwórz nową tabelę i dodaj te pola jako wymiary:
-
Product
-
Jan 2021
-
Feb 2021
-
Mar 2021
-
Apr 2021
-
May 2021
-
Jun 2021
Produkt | 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 |
Skrypt umożliwia tworzenie tabeli krzyżowej z jedną kolumną na każdy miesiąc i jednym wierszem na produkt. Analiza tych danych w obecnym formacie nie jest łatwa. O wiele lepiej byłoby mieć wszystkie liczby w jednym polu, a wszystkie miesiące w innym, w trzykolumnowej tabeli. W następnej sekcji wyjaśniono, jak wykonać tę transformację tabeli krzyżowej.
Drugi skrypt ładowania (funkcja nie jest stosowana)
Usuń komentarz ze skryptu, usuwając //. Skrypt ładowania powinien wyglądać następująco:
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;
Aby uzyskać więcej informacji o ładowaniu wbudowanym, zobacz temat Ładowania wbudowane.
Wyniki
Załaduj dane i otwórz arkusz. Utwórz nową tabelę i dodaj te pola jako wymiary:
-
Product
-
Month
-
Sales
Produkt | Miesiąc | 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 |
Po zastosowaniu prefiksu skryptu tabela krzyżowa jest przekształcana w tabelę prostą z jedną kolumną Month, a drugą — Sales. Zwiększa to czytelność danych.
Przykład 2 – przekształcenie przestawianych danych celów sprzedaży w pionową strukturę tabeli (średnio zaawansowany)
Przegląd
Otwórz Edytor ładowania danych i poniżej dodaj skrypt ładowania do nowej karty.
Skrypt ładowania zawiera:
-
Zestaw danych załadowany do tabeli o nazwie Targets.
-
Prefiks ładowania crosstable, który transponuje imiona i nazwiska sprzedawców w przestawieniu na własne pole oznaczone etykietą Sales Person.
-
Powiązane dane celu sprzedaży, które są uporządkowane w polu o nazwie Target.
Skrypt ładowania
SalesTargets:
CROSSTABLE([Sales Person],Target,1)
LOAD
*
INLINE [
Area, Lisa, James, Sharon
APAC, 1500, 1750, 1850
EMEA, 1350, 950, 2050
NA, 1800, 1200, 1350
];
Wyniki
Załaduj dane i otwórz arkusz. Utwórz nową tabelę i dodaj te pola jako wymiary:
-
Area
-
Sales Person
Dodaj tę miarę:
=Sum(Target)
Obszar | Sprzedawca | =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 |
Jeśli chcesz powielić wyświetlanie danych jako przestawną tabelę wejściową, możesz utworzyć równoważną tabelę przestawną w arkuszu.
Wykonaj następujące czynności:
- Skopiuj i wklej właśnie utworzoną tabelę do arkusza.
- Przeciągnij obiekt wykresu tabeli przestawnej na nowo utworzoną kopię tabeli. Wybierz Konwertuj.
- Kliknij przycisk Koniec edycji.
- Przeciągnij pole Sales Person z pionowej półki kolumnowej na poziomą półkę kolumnową.
Poniższa tabela przedstawia dane w ich początkowej formie tabeli, tak jak są one wyświetlane w Qlik Sense:
Obszar | Sprzedawca | =Sum(Target) |
---|---|---|
Sumy | - | 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 |
Odpowiednia tabela przestawna wygląda podobnie do poniższej, przy czym kolumna z imieniem i nazwiskiem każdego sprzedawcy znajduje się w większym wierszu dla Sales Person:
Obszar | James | Lisa | Sharon |
---|---|---|---|
APAC | 1750 | 1500 | 1850 |
EMEA | 950 | 1350 | 2050 |
NA | 1350 | 1350 | 1350 |
Przykład 3 – przekształcenie przestawianych danych celów sprzedaży w pionową strukturę tabeli (zaawansowany)
Przegląd
Otwórz Edytor ładowania danych i poniżej dodaj skrypt ładowania do nowej karty.
Skrypt ładowania zawiera:
-
Zestaw danych reprezentujący dane dotyczące sprzedaży i celów, uporządkowany według obszaru i miesiąca roku. Ten zestaw danych jest ładowany do tabeli o nazwie SalesAndTargets.
-
Prefiks ładowania crosstable. Służy do przestawiania wymiaru Month Year do dedykowanego pola, a także do transpozycji macierzy sprzedaży i kwot docelowych do dedykowanego pola o nazwie Amount.
-
Konwersja pola Month Year z tekstu na właściwą datę przy użyciu funkcji konwersji tekstu na datę date#. To pole Month Year przekonwertowane na datę jest dołączane z powrotem do tabeli SalesAndTarget za pomocą prefiksu ładowania Join.
Skrypt ładowania
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;
Wyniki
Załaduj dane i otwórz arkusz. Utwórz nową tabelę i dodaj te pola jako wymiary:
-
Area
-
Month Year
Utwórz następującą miarę z etykietą Actual:
=Sum({<Type={'Actual'}>} Amount)
Utwórz także następującą miarę z etykietą 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 |
Jeśli chcesz powielić wyświetlanie danych jako przestawną tabelę wejściową, możesz utworzyć równoważną tabelę przestawną w arkuszu.
Wykonaj następujące czynności:
- Skopiuj i wklej właśnie utworzoną tabelę do arkusza.
- Przeciągnij obiekt wykresu tabeli przestawnej na nowo utworzoną kopię tabeli. Wybierz Konwertuj.
- Kliknij przycisk Koniec edycji.
- Przeciągnij pole Month Year z pionowej półki kolumnowej na poziomą półkę kolumnową.
- Przeciągnij element Values z poziomej półki kolumnowej na pionową półkę kolumnową.
Poniższa tabela przedstawia dane w ich początkowej formie tabeli, tak jak są one wyświetlane w Qlik Sense:
Area | Month Year | Actual | Target |
---|---|---|---|
Sumy | - | 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 |
Odpowiednia tabela przestawna wygląda podobnie do poniższej, przy czym kolumna z poszczególnymi miesiącami znajduje się w większym wierszu dla Month Year:
Area (wartości) | 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 |