Crosstable
crosstable-laddningsprefixet används för att transponera strukturerad data med ”korstabell” eller ”pivottabell”. Data som är strukturerad på detta sätt påträffas ofta när vid arbete med kalkylarkskällor. Resultatet och syftet med crosstable-laddningsprefixet är att omvandla sådana strukturer till en vanlig kolumnorienterad tabellekvivalent, eftersom denna struktur i allmänhet är bättre lämpad för analys i Qlik Sense.
Syntax:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
Argument | Beskrivning |
---|---|
attribute field name | Det önskade utdatafältets namn som beskriver den horisontellt orienterade dimensionen som ska transponeras (rubrikraden). |
data field name |
Det önskade utdatafältets namn som beskriver den horisontellt orienterade dimensionen som ska transponeras (matris av datavärden under rubrikraden). |
n |
Antalet kvalificerarfält eller oförändrade dimensioner som föregår den tabell som ska omvandlas till generisk form. Standardvärdet är 1. |
Skriptingfunktionen är relaterad till följande funktioner:
Funktion | Interaktion |
---|---|
Generic | Ett laddningsprefix för omvandling som tar en strukturerad datamängd av entitetsattributvärde och omvandlar den till en vanlig relationstabellstruktur, som separerar varje attribut som påträffas i ett nytt fält eller kolumn med data. |
Exempel 1 – Omvandla pivoterade försäljningsdata (enkla)
Översikt
Öppna skriptredigeraren och lägg till laddningsskriptet nedan till en ny flik.
Det första laddningsskriptet innehåller en datauppsättning på vilken skriptprefixet crosstable kommer att tillämpas senare, med avsnittet som tillämpar crosstable kommenterat. Detta betyder att kommentarsyntax användes för att inaktivera det här avsnittet i laddningsskriptet.
Det andra laddningsskriptet är detsamma som det första, men med tillämpning av crosstable okommenterat (aktiveras genom att ta bort kommentarsyntaxen). Skripten visas på detta sätt för att markera värdet av denna skriptfunktion vid omvandling av data.
Första laddningsskriptet (funktionen tillämpas inte)
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;
Mer information om hur du använder inline-laddningar finns i Använda inline-laddningar för att ladda data.
Resultat
Ladda data och öppna ett ark. Skapa en ny tabell och lägg till dessa fält som dimensioner:
-
Product
-
Jan 2021
-
Feb 2021
-
Mar 2021
-
Apr 2021
-
May 2021
-
Jun 2021
Produkt | Jan 2021 | Feb 2021 | Mar 2021 | Apr 2021 | Maj 2021 | Jun 2021 |
---|---|---|---|---|---|---|
A | 100 | 98 | 103 | 63 | 108 | 82 |
B | 284 | 279 | 297 | 305 | 294 | 292 |
C | 50 | 53 | 50 | 54 | 49 | 51 |
Det här skriptet gör det möjligt att skapa en korstabell med en kolumn för varje månad och en rad per produkt. I det nuvarande formatet är det inte lätt att analysera informationen. Det skulle vara mycket bättre att ha alla siffror i ett fält och alla månader i ett annat, det vill säga i en tabell med tre kolumner. Nästa avsnitt förklarar hur du gör denna omvandling till korstabell.
Andra laddningsskriptet (funktionen tillämpas)
Avkommentera skriptet genom att ta bort //. Det laddade skriptet bör nu se ut så här:
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;
Mer information om hur du använder inline-laddningar finns i Använda inline-laddningar för att ladda data.
Resultat
Ladda data och öppna ett ark. Skapa en ny tabell och lägg till dessa fält som dimensioner:
-
Product
-
Month
-
Sales
Produkt | Månad | Försäljning |
---|---|---|
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 |
När skriptprefixet har tillämpats så omvandlas korstabellen till en rak tabell med en kolumn för Month och en annan för Sales. Detta förbättrar läsbarheten av data.
Exempel 2 – Omvandling av pivoterade försäljningsmåldata till en vertikal tabellstruktur (mellanliggande)
Översikt
Öppn skriptredigeraren och lägg till laddningsskriptet nedan till en ny flik.
Laddningsskriptet innehåller:
-
En datauppsättning läses in i en tabell som heter Mål.
-
crosstable-laddningsprefixet, som överför de pivoterade säljarnamnen till ett eget fält, märkt Sales Person.
-
Den associerade försäljningsmåldata, som är strukturerade i ett fält som heter Target.
Laddningsskript
SalesTargets:
CROSSTABLE([Sales Person],Target,1)
LOAD
*
INLINE [
Area, Lisa, James, Sharon
APAC, 1500, 1750, 1850
EMEA, 1350, 950, 2050
NA, 1800, 1200, 1350
];
Resultat
Ladda data och öppna ett ark. Skapa en ny tabell och lägg till dessa fält som dimensioner:
-
Area
-
Sales Person
Lägg till måttet:
=Sum(Target)
Yta | Säljare | =Sum(Target) |
---|---|---|
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
Ej tillämpligt | James | 1200 |
Ej tillämpligt | Lisa | 1800 |
Ej tillämpligt | Sharon | 1350 |
Om du vill replikera visningen av data som en pivoterad inmatningstabell kan du skapa ett motsvarande pivottabell i ett ark.
Gör följande:
- Kopiera och klistra in tabellen du just har skapat i arket.
- Dra diagramobjektet för pivottabellen upp på den nyligen skapade tabellkopian. Välj Konvertera.
- Klicka på Redigering klar.
- Dra Sales Person-fältet från den vertikala kolumnhyllan till den horisontella kolumnhyllan.
Följande tabell visar data i sin ursprungliga tabellform, som den visas i Qlik Sense:
Yta | Säljare | =Sum(Target) |
---|---|---|
Totalvärden | - | 13800 |
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
Ej tillämpligt | James | 1200 |
Ej tillämpligt | Lisa | 1800 |
Ej tillämpligt | Sharon | 1350 |
Den motsvarande pivottabellen liknar följande, där kolumnen för varje säljares namn finns i den större raden för Sales Person:
Yta | James | Lisa | Sharon |
---|---|---|---|
APAC | 1750 | 1500 | 1850 |
EMEA | 950 | 1350 | 2050 |
Ej tillämpligt | 1350 | 1350 | 1350 |
Exempel 3 – Omvandling av pivoterade försäljningsmåldata till en vertikal tabellstruktur (mellanliggande)
Översikt
Öppn skriptredigeraren och lägg till laddningsskriptet nedan till en ny flik.
Laddningsskriptet innehåller:
-
En datauppsättning som representerar försäljnings- och måldata, organiserad efter område och månad på året. Detta läses in i en tabell som heter SalesAndTargets.
-
crosstable-laddningsprefixet. Detta används för att avpivotera Month Year-dimensionen till ett dedikerat fält, samt för att överföra matrisen av försäljnings- och målbelopp till ett dedikerat fält som kallas Amount.
-
En konvertering av Month Year-fältet från text till ett korrekt datum, med hjälp av text-till-datum-konverteringsfunktionen date#. Detta datumkonverterade Month Year-fält kopplas tillbaka till SalesAndTarget-tabellen via ett Join-laddningsprefix.
Laddningsskript
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;
Resultat
Ladda data och öppna ett ark. Skapa en ny tabell och lägg till dessa fält som dimensioner:
-
Area
-
Month Year
Skapa följande mått med Actual-etiketten:
=Sum({<Type={'Actual'}>} Amount)
Skapa även måttet med Target-etiketten:
=Sum({<Type={'Target'}>} Amount)
Yta | Månad år | Faktisk | Mål |
---|---|---|---|
APAC | Jan-22 | 435 | 425 |
APAC | Feb-22 | 434 | 425 |
APAC |
Mar-22 | 397 | 425 |
APAC | Apr-22 | 404 | 425 |
APAC | Maj-22 | 458 | 425 |
APAC | Jun-22 | 447 | 425 |
APAC | Jul-22 | 413 | 425 |
APAC | Aug-22 | 458 | 425 |
APAC | Sep-22 | 385 | 425 |
APAC | Okt-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 |
Om du vill replikera visningen av data som en pivoterad inmatningstabell kan du skapa ett motsvarande pivottabell i ett ark.
Gör följande:
- Kopiera och klistra in tabellen du just har skapat i arket.
- Dra diagramobjektet för pivottabellen upp på den nyligen skapade tabellkopian. Välj Konvertera.
- Klicka på Redigering klar.
- Dra Month Year-fältet från den vertikala kolumnhyllan till den horisontella kolumnhyllan.
- Dra Values-elementet från den horisontella kolumnhyllan till den vertikala kolumnhyllan.
Följande tabell visar data i sin ursprungliga tabellform, som den visas i Qlik Sense:
Yta | Månad år | Faktisk | Mål |
---|---|---|---|
Totalvärden | - | 13812 | 13950 |
APAC | Jan-22 | 435 | 425 |
APAC | Feb-22 | 434 | 425 |
APAC |
Mar-22 | 397 | 425 |
APAC | Apr-22 | 404 | 425 |
APAC | Maj-22 | 458 | 425 |
APAC | Jun-22 | 447 | 425 |
APAC | Jul-22 | 413 | 425 |
APAC | Aug-22 | 458 | 425 |
APAC | Sep-22 | 385 | 425 |
APAC | Okt-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 |
Den motsvarande pivottabellen liknar följande, där kolumnen för varje enskild månad för året finns i den större raden för Month Year:
Område (värden) | Jan-22 | Feb-22 | Mar-22 | Apr-22 | Maj-22 | Jun-22 | Jul-22 | Aug-22 | Sep-22 | Okt-22 | Nov-22 | Dec-22 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
APAC – faktisk | 435 | 434 | 397 | 404 | 458 | 447 | 413 | 458 | 385 | 421 | 448 | 397 |
APAC – mål | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 |
EMEA – faktisk | 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 – mål | 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 – faktisk | 378 | 415 | 363 | 356 | 403 | 343 | 401 | 365 | 393 | 340 | 360 | 405 |
EMEA – mål | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 |