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 的區段標記為註解。這表示註解語法已在載入指令碼中用來停用此區段。
第二個載入指令碼與第一個相同,但 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 |
此指令碼允許建立跨維度資料表,其中每個月份都有一欄,而每個產品都有一列。在目前格式中,此資料不容易分析。比較好的做法會是某個欄位中具備所有數字以及另一個欄位 (即三欄表格) 中具備所有月份。下一區段說明如何對跨維度資料表進行此轉換。
第二個載入指令碼 (已套用函數)
移除 // 以為指令碼取消註解。載入指令碼應如下所示:
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 |
套用指令碼前置詞後,跨維度資料表已轉換為直式表格,其中 Month 為一欄,Sales 為另一欄。這可改善資料可讀性。
範例 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 的專用欄位。
-
Month Year 欄位使用文字至日期轉換函數 date# 從文字轉換為正確日期。此日期轉換的 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 |