使用内部记录函数Peek、Previous 和 Exists
当对当前记录的评估需要一个来自以前加载的数据记录的值时,使用这些函数。
在此部分教程中,我们将检测 Peek()、Previous() 和 Exists() 函数。
Peek()
Peek() 用于在表格中返回已经加载行的字段值。可以将行号指定为表格。如果未指定行号,将使用上次加载的记录。
语法:
行必须为整数。0 表示第一个记录,1 表示第二个记录,以此类推。负数表示从表格末端开始计算的顺序。-1 表示最后读取的记录。
如果未陈述行,则假定为 -1。
Tablename 是表格标签,不以冒号结束。如果未指定tablename,则假定为当前表格。如果用于 LOAD 语句之外或指向另外一个表格,则必须包括 tablename。
Previous()
Previous() 用于查找使用因 where 子句而未丢弃的以前输入记录的数据的 expr 表达式的值。在内部表格的首个记录中,此函数将返回 NULL 值。
语法:
可以嵌套 Previous() 函数以访问能够进一步回滚的记录。数据直接从输入源获取,使其也可引用尚未载入 Qlik Sense 的字段,也就是说即使尚未将它们存储在相关的数据库中。
Exists()
Exists() 用于确定是否已经将特定字段值加载到数据加载脚本中的字段。此函数用于返回 TRUE 或 FALSE,这样它可以用于 LOAD 语句或 IF 语句中的 where 子句。
语法:
字段到目前为止必须存在于由脚本加载的数据中。Expression 是对字段值的表达式求值,以在指定字段中查找。如果省略,指定字段中的当前记录值将被假定。
使用 Peek() 和 Previous()
按它们的最简单形式,Peek() 和 Previous() 用于识别表格内的特定值。下面是您将在本练习中加载的 Employees 表中数据的示例。
日期 | Hired | Terminated |
---|---|---|
1/1/2011 | 6 | 0 |
2/1/2011 | 4 | 2 |
3/1/2011 | 6 | 1 |
4/1/2011 | 5 | 2 |
目前,这只能用于收集月份、雇用和终止的相关数据,因此我们要使用 Peek() 和 Previous() 函数为 Employee Count 和 Employee Var 添加字段,用于查看员工总数的每月差异。
执行以下操作:
- 打开高级脚本编写教程应用程序。
- 在数据加载编辑器中新增脚本段。
- 调用部分 Employees。
-
在右菜单的 DataFiles 下,单击选择数据。
- 上传然后选择 Employees.xlsx。
- 在选择数据自窗口中,单击插入脚本。
-
修改脚本,使其如下所示:
[Employees Init]: LOAD rowno() as Row, Date(Date) as Date, Hired, Terminated, If(rowno()=1, Hired-Terminated, peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count] FROM [lib://DataFiles/Employees.xlsx] (ooxml, embedded labels, table is Sheet1);
- 将以下内容添加至您脚本的末尾:
- 单击加载数据。
您的脚本应如下所示:
Excel 工作表中 Date 字段里的日期的格式为 MM/DD/YYYY。要确保使用来自系统变量的格式正确解释日期,将 Date 函数应用至 Date 字段。
Peek() 函数用于识别为所定义字段加载的任何值。在该表达式中,我们将先确定 rowno() 是否等于 1。如果等于 1,则 Employee Count 将不存在,因此我们使用 Hired 减去 Terminated 的差额填充该字段。
如果 rowno() 大于 1,我们查看最后一个月的 Employee Count,然后使用该数字加上该月的 Hired 减去 Terminated 员工的差额。
另请注意,在 Peek() 函数中,我们使用 (-1)。这意味着 Qlik Sense 要查看当前记录上面的记录。如果未指定 (-1),Qlik Sense 将假定您想要查看之前的记录。
[Employee Count]:
LOAD
Row,
Date,
Hired,
Terminated,
[Employee Count],
If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;
Drop Table [Employees Init];
Previous() 函数用于识别为所定义字段加载的上一个值。在该表达式中我们将先确定 rowno() 是否等于 1。如果等于 1,我们知道 Employee Var 将不会存在,因为上一个月的 Employee Count 没有任何记录。因此我们只能为该值输入 0
如果 rowno() 大于 1,我们知道将会存在 Employee Var,因此我们要查看上一个月的 Employee Count,然后从当前月份的 Employee Count 减去该数字,得出 Employee Var 字段中的值。
您的脚本应如下所示:
[Employees Init]:
LOAD
rowno() as Row,
Date(Date) as Date,
Hired,
Terminated,
If(rowno()=1, Hired-Terminated, peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]
FROM [lib://DataFiles/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Employee Count]:
LOAD
Row,
Date,
Hired,
Terminated,
[Employee Count],
If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;
Drop Table [Employees Init];
在应用程序概述的新工作表中,使用 Date, Hired, Terminated, Employee Count 和 Employee Var 作为表的列创建表。 最终生成的表格将如下所示:
Peek() 和 Previous() 可让用户在表格中针对性地定义行。两个函数之间的最大区别在于,Peek() 函数可让用户查看之前尚未加载到脚本的字段,而 Previous() 函数只能让用户查看之前加载的字段。Previous() 在 LOAD 语句的输入中操作,而 Peek() 在 LOAD 语句的输出中操作。(和 RecNo() 与 RowNo() 之间的差值相同)这意味着如果您使用 Where 子句,则这两个函数的作用不同。
因此,当您需要显示当前值与上一个值时更适合使用 Previous() 函数。在本例中,我们计算了每个月的员工变动。
当您针对之前尚未加载到表格的字段或如果您需要针对特定行时,更适合使用 Peek() 函数。如本例所示,我们通过查看上一个月的 Employee Count,然后加上当前月的雇用和终止员工之间的差额计算 Employee Count。请记住,Employee Count 不是原始文件中的一个字段
使用 Exists()
Exists() 函数通常与脚本中的 Where 子句搭配使用以加载数据,如果已经在数据模型中加载相关数据。
在下面的示例中,我们也将使用 Dual() 函数将数值赋值给字符串。
执行以下操作:
- 创建一个新应用程序并为其指定一个名称。
- 在数据加载编辑器中新增脚本段。
- 调用部分 People。
- 输入以下脚本:
- 单击加载数据。
- 创建一个新工作表并为其指定一个名称。
- 打开新表格,然后单击编辑工作表。
- 使用维度 AgeBucket 将标准表格添加至工作表,并将可视化命名为 Age Groups。
- 使用维度 AgeBucket 和度量 Count([AgeBucket]) 将条形图添加到表格。命名可视化 Number of people in each age group。
- 将表格和条形图的属性添加到首选项,然后单击完成。
现在,您的工作表应如下所示:
//Add dummy people data
PeopleTemp:
LOAD * INLINE [
PersonID, Person
1, Jane
2, Joe
3, Shawn
4, Sue
5, Frank
6, Mike
7, Gloria
8, Mary
9, Steven,
10, Bill
];
//Add dummy age data
AgeTemp:
LOAD * INLINE [
PersonID, Age
1, 23
2, 45
3, 43
4, 30
5, 40
6, 32
7, 45
8, 54
9,
10, 61
11, 21
12, 39
];
//LOAD new table with people
People:
NoConcatenate LOAD
PersonID,
Person
Resident PeopleTemp;
Drop Table PeopleTemp;
//Add age and age bucket fields to the People table
Left Join (People)
LOAD
PersonID,
Age,
If(IsNull(Age) or Age='', Dual('No age', 5),
If(Age<25, Dual('Under 25', 1),
If(Age>=25 and Age <35, Dual('25-34', 2),
If(Age>=35 and Age<50, Dual('35-49' , 3),
If(Age>=50, Dual('50 or over', 4)
))))) as AgeBucket
Resident AgeTemp
Where Exists(PersonID);
DROP Table AgeTemp;
在脚本中,Age 和 AgeBucket 字段只有已在数据模型中加载 PersonID 后才加载。
注意,在 AgeTemp 表格中已经为 PersonID 11 和 12 列出年龄,但由于在数据模型(People 表格)中尚未加载这些 ID,因此它们已被 Where Exists(PersonID) 子句排除。该子句语法也可以这样书写:Where Exists(PersonID, PersonID)。
脚本的输出和以下类似:
如果没有将 AgeTemp 表格中的任何 PersonID 加载到数据模型,则不会将 Age 和 AgeBucket 字段联接到 People 表格。使用 Exists() 函数可以帮助防止在数据模型中孤立记录/数据,即 Age 和 AgeBucket 字段没有任何相关的人员。
如果需要将数值赋值给字符串,Dual() 函数在脚本或图表表达式中很有用。
在以上脚本中,您必须拥有用于加载年龄的应用程序,并且您已决定将这些年龄存放在存储段中,这样您就能够根据年龄存储段和实际年龄创建可视化效果。存储段分为 25 岁以下,25 至 35 岁之间等。通过使用 Dual() 函数,可以向年龄存储段分配数值,稍后用来在列表框或图表中对年龄存储段进行排序。因此,如应用程序表格所示,排序后会将“无年龄”放在列表的末尾。