Crosstable
crosstable ロード プレフィックスは、「クロス テーブル」または「ピボット テーブル」の構造化データを転置するために使用されます。このように構造化されたデータは、スプレッドシート ソースを操作するときによく見られます。crosstable ロード プレフィックスの出力と目的は、このような構造を通常の列指向のテーブルに変換することです。これは、この構造のほうが Qlik Sense での分析に適しているためです。
構文:
crosstable (attribute field name, data field name [ , n ] ) ( loadstatement | selectstatement )
引数 | 説明 |
---|---|
attribute field name | 転置される水平方向の軸 (ヘッダー行) を説明する目的の出力項目名。 |
data field name |
転置される軸の水平方向のデータ (ヘッダー行の下のデータ値のマトリクス) をを説明する目的の出力項目名。 |
n |
汎用的な形式に変換されるテーブルに先行する修飾子項目、または変更されなかった軸の数。既定値は 1 です。 |
このスクリプト関数は、次の関数に関連しています。
関数 | 相互作用 |
---|---|
Generic | エンティティ属性値の構造化データ セットを取得し、それを通常のリレーショナル テーブル構造に変換して、検出された各属性をデータの新しい項目または列に分離する変換ロード プレフィックス。 |
例 1 – ピボットされた売上データの変換 (単純)
概要
データ ロード エディターを開き、以下の最初のロード スクリプトを新しいタブに追加します。
最初のロード スクリプトには、crosstable スクリプト プレフィックスが後で適用されるデータセットが含まれており、crosstable を適用するセクションはコメント アウトされています。これは、ロード スクリプトでこのセクションを無効にするためにコメント構文が使用されたことを意味します。
2 番目のロード スクリプトは最初のスクリプトと同じですが、 crosstable の適用がコメント解除されています (コメント構文を削除することで有効になります)。スクリプトがこう表示されているのは、データの変換におけるこのスクリプト関数の価値を強調するためです。
最初のロード スクリプト (関数は適用されません)
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;
インライン ロードの使用の詳細については、インライン ロード を参照してください。
結果
データをロードしてシートを開きます。新しいテーブルを作成し、これらの項目を軸として追加します:
-
Product
-
Jan 2021
-
Feb 2021
-
Mar 2021
-
Apr 2021
-
May 2021
-
Jun 2021
製品 | 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 |
このスクリプトは、月ごとに 1 つの列、製品ごとに 1 つの行を持つ crosstable を示しています。現在の形式では、このデータを分析するのは簡単ではありません。すべての数値を 1 つの項目に、すべての月を別の項目、3 列のテーブルに含める方がはるかに良いでしょう。次のセクションでは、この変換を crosstable に対して行う方法について説明します。
2 番目のロード スクリプト (関数が適用されます)
// を削除して、スクリプトのコメントを解除します。これで、ロード スクリプトは次のようになります。
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;
インライン ロードの使用の詳細については、インライン ロード を参照してください。
結果
データをロードしてシートを開きます。新しいテーブルを作成し、これらの項目を軸として追加します:
-
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 |
スクリプト プレフィックスが適用されると、crosstable は Month に 1 つの列、Sales に 1 つの列を持つストレート テーブルに変換されます。これにより、データが読みやすくなります。
例 2 – ピボットされた売上目標データを垂直テーブル構造に変換する (中間)
概要
データ ロード エディターを開き、以下のロード スクリプトを新しいタブに追加します。
ロード スクリプトには次が含まれています。
-
「Targets」というテーブルにロードされるデータセット。
-
crosstable ロード プレフィックス。ピボットされた営業担当者の名前を Sales Personというラベルの付いた独自の項目に置き換えます。
-
Targetという項目に構造化された、関連する売上目標データ。
ロード スクリプト
SalesTargets:
CROSSTABLE([Sales Person],Target,1)
LOAD
*
INLINE [
Area, Lisa, James, Sharon
APAC, 1500, 1750, 1850
EMEA, 1350, 950, 2050
NA, 1800, 1200, 1350
];
結果
データをロードしてシートを開きます。新しいテーブルを作成し、これらの項目を軸として追加します:
-
Area
-
Sales Person
このメジャーを追加します。
=Sum(Target)
エリア | 営業 担当者 | =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 |
ピボットされた入力テーブルとしてデータの表示を複製する場合は、シートに同等のピボット テーブルを作成できます。
次の手順を実行します。
- 作成したテーブルをコピーしてシートに貼り付けます。
- ピボット テーブル チャート オブジェクトを、新しく作成したテーブル コピーの上にドラッグします。[変換]] を選択します。
- [編集の完了] をクリックします。
- Sales Person 項目を垂直列シェルフから水平列シェルフにドラッグします。
次の表は、Qlik Senseに表示されているように、最初の表形式のデータを示しています。
エリア | 営業 担当者 | =Sum(Target) |
---|---|---|
合計 | - | 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 |
同等のピボット テーブルは次のように、各営業担当者の名前の列は Sales Personの大きな行に含まれる形式になります。
エリア | James | Lisa | Sharon |
---|---|---|---|
APAC | 1750 | 1500 | 1850 |
EMEA | 950 | 1350 | 2050 |
NA | 1350 | 1350 | 1350 |
例 3 – ピボットされた売上および目標データを垂直テーブル構造に変換する (上級)
概要
データ ロード エディターを開き、以下のロード スクリプトを新しいタブに追加します。
ロード スクリプトには次が含まれています。
-
地域と月ごとに編成された売上と目標のデータを表すデータセット。これは、「SalesAndTargets」というテーブルにロードされます。
-
crosstable ロード プレフィックス。これは、Month Year 軸を専用項目にピボット解除するために使用され、売上と目標金額のマトリックスを Amountと呼ばれる専用項目に転置するのにも使用されます。
-
テキストから日付への変換関数 date#を使用した、テキストから適切な日付への Month Year 項目の変換。この日付変換された Month Year 項目は、Join ロード プレフィックスを介して SalesAndTarget テーブルに結合されます。
ロード スクリプト
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;
結果
データをロードしてシートを開きます。新しいテーブルを作成し、これらの項目を軸として追加します:
-
Area
-
Month Year
次のメジャーを、ラベル Actual を使って作成します。
=Sum({<Type={'Actual'}>} Amount)
またこのメジャーを、ラベル Target を使って作成します。
=Sum({<Type={'Target'}>} Amount)
エリア | 月 年 | 実績 | 対象 |
---|---|---|---|
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 |
ピボットされた入力テーブルとしてデータの表示を複製する場合は、シートに同等のピボット テーブルを作成できます。
次の手順を実行します。
- 作成したテーブルをコピーしてシートに貼り付けます。
- ピボット テーブル チャート オブジェクトを、新しく作成したテーブル コピーの上にドラッグします。[変換]] を選択します。
- [編集の完了] をクリックします。
- Month Year 項目を垂直列シェルフから水平列シェルフにドラッグします。
- Values アイテムを、垂直列シェルフから水平列シェルフにドラッグします。
次の表は、Qlik Sense に表示されているように、最初のテーブル形式のデータを示しています。
エリア | 月 年 | 実績 | 対象 |
---|---|---|---|
合計 | - | 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 |
同等のピボット テーブルは次のように、その年の個別の月の列は Month Yearの大きな行に含まれる形式になります。
エリア (値) | 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 - 実績 | 435 | 434 | 397 | 404 | 458 | 447 | 413 | 458 | 385 | 421 | 448 | 397 |
APAC - 目標 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 | 425 |
EMEA - 実績 | 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 - 目標 | 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 - 実績 | 378 | 415 | 363 | 356 | 403 | 343 | 401 | 365 | 393 | 340 | 360 | 405 |
NA - 目標 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 | 375 |