資料清理
有時,您載入 Qlik Sense 的來源資料不一定是您想在 Qlik Sense 應用程式中顯示的樣子。Qlik Sense 提供了多種函數和陳述式,以方便您將資料轉換成我們想要的格式。
執行指令碼時,可以在 Qlik Sense 指令碼中使用對應來取代或修改欄位值或名稱,因此對應可用於清理資料,並確保更高的一致性,或者取代部分或全部欄位值。
從不同的表格載入資料時,代表相同事物的欄位值的命名不一定會一致。因為缺乏一致性會阻礙關聯,所以需要解決這個問題。透過建立欄位值比較的對應表格,即可漂亮地解決此問題。
對應表格
透過 Mapping 載入或 Mapping 選取載入的表格,與其他表格的處理方式有所不同。它們會儲存在記憶體的不同區域,並且僅在指令碼執行時作為對應表格之用。執行指令碼後,這些表格會被自動捨棄。
規則:
- 對應表格必須有兩個資料行:第一個包含比較值,第二個包含所需的對應值。
- 必須對這兩個資料行加以命名,但名稱本身沒有相關性。該資料行名稱和一般內部表格的欄位名稱並無關聯。
Mapping 函數和陳述式
本教學課程中將說明以下對應函數/陳述式:
•Mapping 前置詞
• ApplyMap()
• MapSubstring()
•Map … Using 陳述式
•Unmap 陳述式
對應前置詞
Mapping 前置詞用於在指令碼中建立對應表格。對應表格可與 ApplyMap() 函數、MapSubstring() 函數或 Map … Using 陳述式一起使用。
請執行下列動作:
- 建立新的應用程式並命名。
- 在資料載入編輯器中新增新的指令碼區段。
- 叫用區段 Countries。
- 輸入以下指令碼:
CountryMap:
MAPPING LOAD * INLINE [
Country, NewCountry
U.S.A., US
U.S., US
United States, US
United States of America, US
];
CountryMap 表格儲存兩個資料行:Country 和 NewCountry。Country 資料行將輸入的各種國家拼寫方式儲存在 Country 欄位中。NewCountry 資料行儲存各個值的對應方式。此對應表格將用於在 Country 欄位中儲存一致的US 國家值。例如,如果 U.S.A. 儲存在 Country 欄位中,將其對應為 US。
ApplyMap() 函數
使用 ApplyMap() 根據之前建立的對應表格取代欄位中的資料。在使用 ApplyMap() 函數之前,需要先載入對應表格。您將載入的 Data.xlsx 表格中的資料如下所示:
ID | 名稱 | 國家/地區 | 代碼 |
---|---|---|---|
1 | John Black | 美國 | SDFGBS1DI |
2 | Steve Johnson | 美國 | 2ABC |
3 | Mary White | 美國 | DJY3DFE34 |
4 | Susan McDaniels | u | DEF5556 |
5 | Dean Smith | US | KSD111DKFJ1 |
請注意國家是以多種方式輸入的。為了確保國家欄位的一致性,已載入對應表格,並使用了 ApplyMap() 函數。
請執行下列動作:
- 在您於上方輸入的指令碼之下,選取並載入 Data.xlsx,然後插入指令碼。
- 在新建立的 LOAD 陳述式之上輸入下列內容:
- 如下所示修改包括 Country, 的行:
- 按一下載入資料。
- 將 'US' 新增為 ApplyMap() 函數的第三個參數,以處理國家沒有正確輸入的情況:
- 按一下載入資料。
Data:
您的指令碼應如下所示:
CountryMap:
MAPPING LOAD * INLINE [
Country, NewCountry
U.S.A., US
U.S., US
United States, US
United States of America, US
];
Data:
LOAD
ID,
Name,
Country,
Code
FROM [lib://DataFiles/Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
ApplyMap('CountryMap', Country) as Country,
ApplyMap() 函數的首個參數的對應名稱用單引號括住了。第二個參數是其中的資料將被取代的欄位。
產生的表格如下所示:
United States 的各種拼寫已全部變更為 US。有一個記錄沒有拼寫正確,因此 ApplyMap() 函數沒有變更該欄位值。使用 ApplyMap() 函數時,如果對應表格沒有配對的值,您可使用第三個參數新增預設運算式。
ApplyMap('CountryMap', Country, 'US') as Country,
您的指令碼應如下所示:
CountryMap:
MAPPING LOAD * INLINE [
Country, NewCountry
U.S.A., US
U.S., US
United States, US
United States of America, US
];
Data:
LOAD
ID,
Name,
ApplyMap('CountryMap', Country, 'US') as Country,
Code
FROM [lib://DataFiles/Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
產生的表格如下所示:
MapSubstring() 函數
透過 MapSubstring() 函數,您可以對應部分欄位。
在 ApplyMap() 建立的表格中,我們想要把數字寫成文字格式,所以將使用 MapSubstring() 函數把數字資料替換成文字。
要執行此操作,首先要建立一個對應表格。
請執行下列動作:
- 在 CountryMap 區段之後新增以下指令碼行,但必須加在 Data 區段之前。
- 在指令碼的 Data 區段,如下所示修改 Code 陳述式:
- 按一下載入資料。
CodeMap:
MAPPING LOAD * INLINE [
F1, F2
1, one
2, two
3, three
4, four
5, five
11, eleven
];
在 CodeMap 表格中,數字 1 至 5 以及 11 都有對應。
MapSubString('CodeMap', Code) as Code
您的指令碼應如下所示:
CountryMap:
MAPPING LOAD * INLINE [
Country, NewCountry
U.S.A., US
U.S., US
United States, US
United States of America, US
];
CodeMap:
MAPPING LOAD * INLINE [
F1, F2
1, one
2, two
3, three
4, four
5, five
11, eleven
];
Data:
LOAD
ID,
Name,
ApplyMap('CountryMap', Country, 'US') as Country,
MapSubString('CodeMap', Code) as Code
FROM [lib://DataFiles/Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
產生的表格如下所示:
在 Code 欄位中,數字字元已被文字取代。如果數字出現多次,如 ID=3 和 ID=4 那樣,則同樣重複文字。ID=4。Susan McDaniels 在其程式碼中有 6。因為 6 沒有在 CodeMap 表格中對應,因此將保持不變。ID=5、Dean Smith 的程式碼中有 111。這已經被對應為 'elevenone'。
Map … Using
也可以使用 Map … Using 陳述式將對應套用至欄位。不過,這與 ApplyMap() 的運作方式有些不同。。ApplyMap() 處理每次欄位名稱遇到的對應問題,而 Map … Using 處理值儲存在內部表格中欄位名稱下遇到的對應問題。
讓我們來看一個例子。假設我們在指令碼中多次載入 Country 欄位,並希望每次載入欄位時,都能套用對應。像本課程教學之前說明的那樣,可以使用 ApplyMap() 函數,也可以使用 Map … Using。
如果使用了 Map … Using,當欄位儲存在內部表格中時,對應將套用至欄位。在下面的範例中,對應被套用至 Data1 表格中的 Country 欄位,但不會被套用至 Data2 表格中的 Country2 欄位。這是因為 Map … Using 陳述式只能套用至名為 Country 的欄位。當 Country2 欄位儲存在內部表格中時,它的名稱不再是 Country。如要將對應套用至 Country2 表格,您必須使用 ApplyMap() 函數。
如果 Country 要在 Unmap 陳述式之後載入,則 Unmap 陳述式將終止 Map … Using 陳述式,系統不會套用 CountryMap。
請執行下列動作:
- 使用下列內容取代 Data 表格的指令碼:
- 按一下載入資料。
Map Country Using CountryMap;
Data1:
LOAD
ID,
Name,
Country
FROM [lib://DataFiles/Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Data2:
LOAD
ID,
Country as Country2
FROM [lib://DataFiles/Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
UNMAP;
產生的表格如下所示: