Crosstable
Het laadvoorvoegsel crosstable wordt gebruikt om gestructureerde gegevens in "kruistabel" of "draaitabel" te transponeren. Gegevens die op deze manier zijn gestructureerd, worden vaak aangetroffen bij het werken met spreadsheetbronnen. De uitvoer en het doel van het laadvoorvoegsel crosstable is om dergelijke structuren om te zetten in een normaal kolomgeoriënteerd tabelequivalent, aangezien deze structuur over het algemeen beter geschikt is voor analyse in Qlik Sense.
Syntaxis:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
Argument | Beschrijving |
---|---|
attribute field name | De gewenste uitvoerveldnaam die de horizontaal georiënteerde dimensie beschrijft die moet worden getransponeerd (de koprij). |
data field name |
De gewenste uitvoerveldnaam die de horizontaal georiënteerde gegevens beschrijft van de dimensie die moet worden getransponeerd (de matrix van gegevenswaarden onder de koprij). |
n |
Het aantal kwalificatievelden, of ongewijzigde dimensies, dat voor de tabel staat, die in generieke vorm moet worden veranderd. De standaardwaarde is 1. |
Deze scriptfunctie is gerelateerd aan de volgende functies:
Functie | Interactie |
---|---|
Generic | Een voorvoegsel voor het laden van de transformatie dat een gestructureerde gegevensset van entiteit-attribuut-waarde neemt en deze omzet in een normale relationele tabelstructuur, waarbij elk gevonden attribuut wordt gescheiden in een nieuw veld of nieuwe kolom met gegevens. |
Voorbeeld 1 – Omzetten van gedraaide verkoopgegevens (eenvoudig)
Overzicht
Open de editor voor laden van gegevens en voeg het eerste onderstaande load-script toe aan een nieuw tabblad.
Het eerste load-script bevat een gegevensverzameling waarop het scriptvoorvoegsel crosstable later zal worden toegepast, waarbij de sectie die van toepassing is op crosstable wordt weggelaten. Dit betekent dat de syntaxis van opmerkingen is gebruikt om deze sectie in het load-script uit te schakelen.
Het tweede load-script is hetzelfde als het eerste, maar met de toepassing van crosstable zonder commentaar (ingeschakeld door de commentaarsyntaxis te verwijderen). De scripts worden op deze manier weergegeven om de waarde van deze scriptfunctie bij het transformeren van gegevens te benadrukken.
Eerste load-script (functie niet toegepast)
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;
Voor meer informatie over inline loads, raadpleegt u Inline loads.
Resultaten
Laad de gegevens en open een werkblad. Maak een nieuwe tabel en voeg deze velden toe als dimensies:
-
Product
-
Jan 2021
-
Feb 2021
-
Mar 2021
-
Apr 2021
-
May 2021
-
Jun 2021
Product | Jan 2021 | Feb 2021 | Mrt 2021 | Apr 2021 | Mei 2021 | Jun 2021 |
---|---|---|---|---|---|---|
A | 100 | 98 | 103 | 63 | 108 | 82 |
B | 284 | 279 | 297 | 305 | 294 | 292 |
C | 50 | 53 | 50 | 54 | 49 | 51 |
Met dit script kan een kruistabel met een kolom voor elke maand en één rij per product worden gemaakt. In de huidige indeling kunnen deze gegevens niet gemakkelijk worden geanalyseerd. Het is veel beter als alle getallen zich in één veld bevinden en alle maanden in een ander veld, in een tabel met drie kolommen. In de volgende sectie wordt uitgelegd hoe u deze transformatie naar de kruistabel kunt uitvoeren.
Tweede load-script (functie toegepast)
Verwijder het commentaar van het script door de // te verwijderen. Het load-script zou er als volgt moeten uitzien:
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;
Voor meer informatie over inline loads, raadpleegt u Inline loads.
Resultaten
Laad de gegevens en open een werkblad. Maak een nieuwe tabel en voeg deze velden toe als dimensies:
-
Product
-
Month
-
Sales
Product | Month | 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 |
Wanneer het scriptvoorvoegsel is toegepast, wordt de kruistabel getransformeerd in een rechte tabel met één kolom voor Month en nog een voor Sales. Dit verbetert de leesbaarheid van de gegevens.
Voorbeeld 2 - Omzetten van gedraaide verkoopdoelgegevens in een verticale tabelstructuur (tussenliggend)
Overzicht
Open de editor voor laden van gegevens en voeg het onderstaande load-script toe aan een nieuw tabblad.
Het load-script bevat:
-
Een gegevensset die wordt geladen in een tabel met de naam Doelen.
-
Het laadvoorvoegsel crosstable, dat de gedraaide verkopersnamen omzet in een eigen veld, met het label Sales Person.
-
De bijbehorende verkoopdoelgegevens, die zijn gestructureerd in een veld met de naam Target.
Load-script
SalesTargets:
CROSSTABLE([Sales Person],Target,1)
LOAD
*
INLINE [
Area, Lisa, James, Sharon
APAC, 1500, 1750, 1850
EMEA, 1350, 950, 2050
NA, 1800, 1200, 1350
];
Resultaten
Laad de gegevens en open een werkblad. Maak een nieuwe tabel en voeg deze velden toe als dimensies:
-
Area
-
Sales Person
Voeg deze meting toe:
=Sum(Target)
Gebied | Verkoop-medewerker | =Sum(Target) |
---|---|---|
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
n.v.t. | James | 1200 |
n.v.t. | Lisa | 1800 |
n.v.t. | Sharon | 1350 |
Als u de weergave van gegevens wilt repliceren als de gedraaide invoertabel, kunt u een equivalente draaitabel in een werkblad maken.
Doe het volgende:
- Kopieer en plak de tabel die u zojuist hebt gemaakt in het werkblad.
- Sleep het diagramobject Draaitabel bovenop de nieuw gemaakte tabelkopie. Selecteer Converteren.
- Klik op Klaar met bewerken.
- Sleep het veld Sales Person van de verticale kolomplank naar de horizontale kolomplank.
De volgende tabel toont de gegevens in de oorspronkelijke tabelvorm, zoals weergegeven in Qlik Sense:
Gebied | Verkoop-medewerker | =Sum(Target) |
---|---|---|
Totalen | - | 13800 |
APAC | James | 1750 |
APAC | Lisa | 1500 |
APAC |
Sharon | 1850 |
EMEA | James | 950 |
EMEA | Lisa | 1350 |
EMEA | Sharon | 2050 |
n.v.t. | James | 1200 |
n.v.t. | Lisa | 1800 |
n.v.t. | Sharon | 1350 |
De equivalente draaitabel ziet er als volgt uit, met de kolom voor de naam van elke verkoper in de grotere rij voor Sales Person:
Gebied | James | Lisa | Sharon |
---|---|---|---|
APAC | 1750 | 1500 | 1850 |
EMEA | 950 | 1350 | 2050 |
n.v.t. | 1350 | 1350 | 1350 |
Voorbeeld 3 - Omzetten van gedraaide verkoop- en doelgegevens in een verticale tabelstructuur (geavanceerd)
Overzicht
Open de editor voor laden van gegevens en voeg het onderstaande load-script toe aan een nieuw tabblad.
Het load-script bevat:
-
Een gegevensset met verkoop- en doelgegevens, geordend op gebied en maand van het jaar. Dit wordt geladen in een tabel genaamd SalesAndTargets.
-
Het laadvoorvoegsel crosstable. Dit wordt gebruikt om het draaien van de dimensie Month Year ongedaan te maken in een speciaal veld en om de matrix van verkoop- en doelbedragen om te zetten in een speciaal veld met de naam Amount.
-
Een conversie van het veld Month Year van tekst naar een goede datum, met behulp van de tekst-naar-datum-conversiefunctie date#. Dit naar datum geconverteerde veld Month Year wordt teruggekoppeld naar de tabel SalesAndTarget via een laadvoorvoegsel Join.
Load-script
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;
Resultaten
Laad de gegevens en open een werkblad. Maak een nieuwe tabel en voeg deze velden toe als dimensies:
-
Area
-
Month Year
Maak de volgende meting, met het label Actual:
=Sum({<Type={'Actual'}>} Amount)
Maak ook deze meting, met het label Target:
=Sum({<Type={'Target'}>} Amount)
Gebied | Maand Jaar | Werkelijk | Doel |
---|---|---|---|
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 |
Als u de weergave van gegevens wilt repliceren als de gedraaide invoertabel, kunt u een equivalente draaitabel in een werkblad maken.
Doe het volgende:
- Kopieer en plak de tabel die u zojuist hebt gemaakt in het werkblad.
- Sleep het diagramobject Draaitabel bovenop de nieuw gemaakte tabelkopie. Selecteer Converteren.
- Klik op Klaar met bewerken.
- Sleep het veld Month Year van de verticale kolomplank naar de horizontale kolomplank.
- Sleep het item Values van de horizontale kolomplank naar de verticale kolomplank.
De volgende tabel toont de gegevens in de oorspronkelijke tabelvorm, zoals weergegeven in Qlik Sense:
Gebied | Maand Jaar | Werkelijk | Doel |
---|---|---|---|
Totalen | - | 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 |
De equivalente draaitabel ziet er als volgt uit, met de kolom voor elke afzonderlijke maand van het jaar in de grotere rij voor Month Year:
Gebied (Waarden) | 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 - Actueel | 435 | 434 | 397 | 404 | 458 | 447 | 413 | 458 | 385 | 421 | 448 | 397 |
APAC - Doel | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 |
EMEA - Actueel | 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 - Doel | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 | 362.5 |
N.v.t. - Actueel | 378 | 415 | 363 | 356 | 403 | 343 | 401 | 365 | 393 | 340 | 360 | 405 |
N.v.t. - Doel | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 |