数据清理
有时候,当您将源数据加载到 Qlik Sense 时,不需要您在 Qlik Sense 应用程序中等待完成。Qlik Sense 提供了一系列函数和语句,用于将数据转换成所需的格式。
当脚本运行时可以在 Qlik Sense 脚本中使用映射来替换或修改字段值或名称,以便于可以使用映射清理数据,并使它更加一致或替换部分或所有的字段值。
当您从不同表格加载数据时,表示相同事物的字段值并不总是拥有一致的名称。由于缺乏一致性会妨碍关联,因而需要解决此问题。这一问题通过创建映射表比较字段值可以得到完美解决。
映射表
通过 Mapping 或 Mapping 加载的表格的处理方式不同于其他表格。它们存储在内存的单独区域内,并在脚本运行时仅用作映射表。在脚本运行后,将自动删除这些表格。
规则:
- 映射表必须拥有列,第一列包含比较值,第二列包含所需的映射值。
- 两列必须命名,且名称之间不存在关联。列名称不得与常规内部表格的字段名称相联系。
Mapping 函数和语句
本教程将详细讨论以下映射函数/语句:
•Mapping 前缀
• ApplyMap()
• MapSubstring()
•Map … Using 语句
•Unmap 语句
Mapping 前缀
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 | Name | Country | Code |
---|---|---|---|
1 | John Black | U.S.A. | SDFGBS1DI |
2 | Steve Johnson | U.S. | 2ABC |
3 | Mary White | 美国 | DJY3DFE34 |
4 | Susan McDaniels | u | DEF5556 |
5 | Dean Smith | US | KSD111DKFJ1 |
注意应以不同方式输入国家/地区。为了使 Country 字段保持一致,应先加载映射表,然后再使用 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。由于在 CodeMap 表中不映射 6,因此它保持不变。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() 函数。
Unmap 语句结束 Map … Using 语句,因此如果在执行 Unmap 语句后加载 Country,将不会应用 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;
最终生成的表格如下所示: