qlik sense How-to-group-numbers-in-range by IntervalMatch function

末蓝、 2023-03-06 05:37 7阅读 0赞

## Background ##

有时候我们需要用qlik对数据进行分组范围匹配,例如匹配60分到80分为good,80分到100分为excellent

> Sometimes we need to use qlik to group the data in range, for example, matching 60 points to 80 points as good, 80 points to 100 points as excellent

## Sulution ##

Score:
    LOAD * Inline [
    Score, Name
    77, Apple
    4, Orange
    99, Banana
    100, GOD
    ];
    
    ScoreRange:
    LOAD * INLINE [
    Start, End, Evaluation
    0, 60, COOL
    61, 80, GOOD
    81, 100, EXCELLENT
    ];
    
    Inner Join IntervalMatch ( Score) 
    LOAD Start, End
    Resident ScoreRange;

result:

by zhengkai.blog.csdn.net
    
    Score, Name,Evaluation
    77, Apple , GOOD
    4, Orange , COOL
    99, Banana, EXCELLENT
    100, GOD, EXCELLENT

## Knowledge ##

The IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals, and optionally matching the values of one or several additional keys.

> IntervalMatch 前缀用于创建表格以便将离散数值与一个或多个数值间隔进行匹配，并且任选匹配一个或多个额外关键值。

Syntax:

*  IntervalMatch (matchfield)(loadstatement | selectstatement )
 *  IntervalMatch (matchfield,keyfield1 \[ , keyfield2, … keyfield5 \] ) (loadstatement | selectstatement )

The IntervalMatch prefix must be placed before a LOAD or a SELECT statement that loads the intervals. The field containing the discrete data points (Time in the example below) and additional keys must already have been loaded into QlikView before the statement with the IntervalMatch prefix. The prefix does not by itself read this field from the database table. The prefix transforms the loaded table of intervals and keys to a table that contains an additional column: the discrete numeric data points. It also expands the number of records so that the new table has one record per possible combination of discrete data point, interval and value of the key field(s).

> IntervalMatch 前缀必须置于加载时间间隔的 LOAD 或 SELECT 语句之前。在使用此语句和 IntervalMatch 前缀之前，包含离散数据点的字段（以下所示的 Time）必须已经加载到 Qlik Sense。此前缀不会从数据库表格中读取此字段。此前缀将加载的时间间隔表格转换为包含其他列（离散数值数据点）的表格。另外其扩展了记录数，以使新表格对离散数据点、时间间隔和关键字段值的每个可能组合都有一条记录。

The intervals may be overlapping and the discrete values will be linked to all matching intervals.

> 时间间隔可以重叠，离散值可以链接所有匹配的时间间隔。

The extended IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals, while at the same time matching the values of one or several additional keys. This is a very powerful and flexible feature that can be used for linking transactions with dimensions that are changing over time: Slowly changing dimensions.

> 使用关键字段扩展 IntervalMatch 前缀时，可用于创建既能将离散数值和一个或多个数值时间间隔匹配的表格，同时又能匹配一个或多个额外关键字段值。

In order to avoid undefined interval limits being disregarded, it may be necessary to allow NULL values to map to other fields that constitute the lower or upper limits to the interval. This can be handled by the NullAsValue statement or by an explicit test that replaces NULL values with a numeric value well before or after any of the discrete numeric data points.

> 要避免未定义的时间间隔限值遭到忽略，可能必须让 NULL 可以映射到构成时间间隔下限或上限的其他字段。这可通过 NullAsValue 语句或显式测试来处理，显式测试可在任何离散数值数据点前后使用数值很好地替代 NULL。

Arguments:

<table> 
 <thead> 
  <tr> 
   <th>Argument</th> 
   <th>Description</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>matchfield</td> 
   <td>The field containing the discrete numeric values to be linked to intervals.</td> 
   <td>一个字段，包含链接至时间间隔的离散数值。</td> 
  </tr> 
  <tr> 
   <td>keyfield</td> 
   <td>Fields that contain the additional attributes that are to be matched in the transformation.</td> 
   <td>一个字段，包含转换中匹配的额外属性。</td> 
  </tr> 
  <tr> 
   <td>loadstatement or selectstatement</td> 
   <td>Must result in a table, where the first field contains the lower limit of each interval, the second field contains the upper limit of each interval, and in the case of using key matching, the third and any subsequent fields contain the keyfield(s) present in the IntervalMatch statement. The intervals are always closed, i.e. the end points are included in the interval. Non-numeric limits render the interval to be disregarded (undefined).</td> 
   <td>必会生成一个表格，其中第一个字段包含每个时间间隔的下半部限制，第二个字段包含每个时间间隔的上半部限制，如果使用关键字匹配，则第三个及此后的任何字段包含显示于 IntervalMatch 语句中的关键字段值。时间间隔总是封闭区间，即间隔的端点包括在时间间隔之中。非数值限值会导致时间间隔遭到忽略（未定义）。</td> 
  </tr> 
 </tbody> 
</table>

EventLog:
    LOAD * Inline [
    Time, Event, Comment
    00:00, 0, Start of shift 1
    01:18, 1, Line stop
    02:23, 2, Line restart 50%
    04:15, 3, Line speed 100%
    08:00, 4, Start of shift 2
    11:43, 5, End of production
    ];
    
    OrderLog:
    LOAD * INLINE [
    Start, End, Order
    01:00, 03:35, A
    02:30, 07:58, B
    03:04, 10:27, C
    07:23, 11:43, D
    ];
    
    //Link the field Time to the time intervals defined by the fields Start and End.
    Inner Join IntervalMatch ( Time ) 
    LOAD Start, End
    Resident OrderLog;

效果

<table> 
 <tbody> 
  <tr> 
   <th>Time </th> 
   <th>Start </th> 
   <th>End </th> 
   <th>Order </th> 
  </tr> 
  <tr> 
   <td> <p>00:00 </p> </td> 
   <td>- </td> 
   <td>- </td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>01:18 </td> 
   <td>01:00 </td> 
   <td>03:35 </td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>02:23 </td> 
   <td>01:00 </td> 
   <td>03:35 </td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>04:15 </td> 
   <td>02:30 </td> 
   <td>07:58 </td> 
   <td>B</td> 
  </tr> 
  <tr> 
   <td>04:15 </td> 
   <td>03:04 </td> 
   <td>10:27 </td> 
   <td>C</td> 
  </tr> 
  <tr> 
   <td>08:00 </td> 
   <td>03:04 </td> 
   <td>10:27 </td> 
   <td>C</td> 
  </tr> 
  <tr> 
   <td>08:00 </td> 
   <td>07:23 </td> 
   <td>11:43 </td> 
   <td>D</td> 
  </tr> 
  <tr> 
   <td>11:43 </td> 
   <td>07:23 </td> 
   <td> <p>11:43 </p> </td> 
   <td>D</td> 
  </tr> 
 </tbody> 
</table>