Qlik Sense中合并重叠日期字段的脚本问题及优化需求

算力阿峰

2026-8-10

合并Qlik Sense中相交日期区间的正确脚本实现

问题背景

原有脚本尝试合并重叠日期区间，但仅检查当前行与前一行的关系，无法处理跨多行的区间合并；修改后的脚本在包含date_open > date_close的异常数据集中仍失效，需求是将所有相交/重叠/包含的日期区间合并，取组内最小date_open和最大date_close。

初始脚本

Table_one:
load *
INLINE [
id_client | id_question| date_open | date_close
YZIYR00R | 14534534 | 05.10.2022 | 30.10.2022
YZIYR00R | 14786543 | 26.10.2022 | 27.10.2022
YZIYR00R | 87634957 | 27.10.2022 | 28.10.2022
YZIYR00R | 12398750 | 27.10.2022 | 28.10.2022
YZIYR00R | 57548555 | 27.10.2022 | 28.10.2022
YZIYR00R | 36485023 | 29.10.2022 | 30.10.2022
] (delimiter is '|');
Tmp:
LOAD *, if(previous(date_close) >= date_open and previous(id_client) = id_client,
peek(question_group), id_question) as question_group
Resident Table_one
ORDER BY id_client, date_open, date_close;
drop table Table_one;
SampleData:
LOAD id_client, question_group as id_question,
FirstSortedValue(date_open, recno()) as date_open,
FirstSortedValue(date_close, -recno()) as date_close
Resident Tmp
GROUP BY id_client, question_group;
drop table Tmp;

初始脚本问题

仅校验相邻行的日期重叠关系，无法处理跨多行的区间合并；未处理date_open > date_close的异常数据，导致部分区间归类错误。

自行修改后的脚本

Table_one:
load *
INLINE [
id_client | id_question| date_open | date_close
YZIYR00R | 14534534 | 11.01.2022 | 11.01.2022
YZIYR00R | 14786543 | 11.01.2022 | 11.01.2022
YZIYR00R | 87634957 | 11.01.2022 | 11.01.2022
YZIYR00R | 12398750 | 11.01.2022 | 12.01.2022
YZIYR00R | 57548555 | 13.01.2022 | 13.01.2022
YZIYR00R | 36485023 | 13.01.2022 | 14.01.2022
YZIYR00R | 09748361 | 13.01.2022 | 13.01.2022
YZIYR00R | 56419453 | 13.01.2022 | 15.01.2022
] (delimiter is '|');
Tmp:
LOAD *, if(previous(date_close) >= date_open and previous(id_client) = id_client,
peek(question_group), id_question) as question_group
Resident Table_one
ORDER BY id_client,date_close,date_open;
drop table Table_one;
next:
load id_client, question_group as id_question, min(date_open) as date_open, max(date_close) as date_close
Resident Tmp
Group by id_client, question_group;
drop table Tmp;

测试失败的数据集

Table_one:
load *
INLINE [
id_client | id_question| date_open | date_close
YZIYR00R | 14534534 | 03.10.2022 | 03.10.2022
YZIYR00R | 14786543 | 04.10.2022 | 04.10.2022
YZIYR00R | 87634957 | 05.10.2022 | 02.12.2022
YZIYR00R | 12398750 | 06.10.2022 | 05.10.2022
YZIYR00R | 57548555 | 08.10.2022 | 06.10.2022
YZIYR00R | 36485023 | 17.10.2022 | 11.10.2022
YZIYR00R | 09748361 | 19.10.2022 | 18.10.2022
YZIYR00R | 56419453 | 20.10.2022 | 19.10.2022
YZIYR00R | 64324123 | 31.10.2022 | 26.10.2022
YZIYR00R | 53634322 | 01.11.2022 | 31.10.2022
YZIYR00R | 56787656 | 03.11.2022 | 03.11.2022
YZIYR00R | 78946487 | 09.11.2022 | 03.11.2022
YZIYR00R | 11111111 | 09.11.2022 | 09.11.2022
YZIYR00R | 98541484 | 10.11.2022 | 11.11.2022
YZIYR00R | 45487874 | 29.11.2022 | 23.11.2022
YZIYR00R | 26548459 | 02.12.2022 | 29.11.2022
] (delimiter is '|');

解决方案

以下脚本先修正日期异常，再通过迭代方式完成所有相交区间的合并：

// 1. 加载原始数据并修正日期异常（确保date_open <= date_close）
Table_one:
LOAD 
    id_client,
    id_question,
    Date(Date#(date_open, 'DD.MM.YYYY')) as date_open,
    Date(Date#(date_close, 'DD.MM.YYYY')) as date_close,
    // 交换异常日期的起始和结束值
    if(Date#(date_open, 'DD.MM.YYYY') > Date#(date_close, 'DD.MM.YYYY'), 
       Date(Date#(date_close, 'DD.MM.YYYY')), 
       Date(Date#(date_open, 'DD.MM.YYYY'))) as fixed_open,
    if(Date#(date_open, 'DD.MM.YYYY') > Date#(date_close, 'DD.MM.YYYY'), 
       Date(Date#(date_open, 'DD.MM.YYYY')), 
       Date(Date#(date_close, 'DD.MM.YYYY'))) as fixed_close
INLINE [
id_client | id_question| date_open | date_close
YZIYR00R | 14534534 | 03.10.2022 | 03.10.2022
YZIYR00R | 14786543 | 04.10.2022 | 04.10.2022
YZIYR00R | 87634957 | 05.10.2022 | 02.12.2022
YZIYR00R | 12398750 | 06.10.2022 | 05.10.2022
YZIYR00R | 57548555 | 08.10.2022 | 06.10.2022
YZIYR00R | 36485023 | 17.10.2022 | 11.10.2022
YZIYR00R | 09748361 | 19.10.2022 | 18.10.2022
YZIYR00R | 56419453 | 20.10.2022 | 19.10.2022
YZIYR00R | 64324123 | 31.10.2022 | 26.10.2022
YZIYR00R | 53634322 | 01.11.2022 | 31.10.2022
YZIYR00R | 56787656 | 03.11.2022 | 03.11.2022
YZIYR00R | 78946487 | 09.11.2022 | 03.11.2022
YZIYR00R | 11111111 | 09.11.2022 | 09.11.2022
YZIYR00R | 98541484 | 10.11.2022 | 11.11.2022
YZIYR00R | 45487874 | 29.11.2022 | 23.11.2022
YZIYR00R | 26548459 | 02.12.2022 | 29.11.2022
] (delimiter is '|');

// 2. 按客户和修正后的起始日期排序
Tmp_Sorted:
LOAD 
    id_client,
    id_question,
    fixed_open as date_open,
    fixed_close as date_close
Resident Table_one
ORDER BY id_client, fixed_open;

DROP Table Table_one;

// 3. 初始化分组并迭代合并区间
Tmp_Merged:
LOAD 
    id_client,
    id_question,
    date_open,
    date_close,
    // 第一行组ID为1，后续行若与上一组重叠则归为同一组
    if(RecNo()=1, 1, 
       if(date_open <= peek('max_close') and id_client = peek('id_client'), 
          peek('group_id'), 
          peek('group_id') + 1)) as group_id,
    date_close as max_close
Resident Tmp_Sorted;

// 循环更新组内最大结束日期，确保所有重叠区间被合并
Do While ScriptError = 0;
    Tmp_Update:
    LOAD 
        id_client,
        group_id,
        max(date_close) as new_max_close
    Resident Tmp_Merged
    GROUP BY id_client, group_id;

    LEFT JOIN(Tmp_Merged)
    LOAD 
        id_client,
        group_id,
        new_max_close
    Resident Tmp_Update;

    DROP Table Tmp_Update;

    // 更新组内最大结束日期
    Tmp_Merged_New:
    LOAD 
        id_client,
        id_question,
        date_open,
        date_close,
        group_id,
        if(RecNo()=1, new_max_close, 
           if(id_client = peek('id_client') and group_id = peek('group_id'), 
              peek('new_max_close'), 
              new_max_close)) as max_close
    Resident Tmp_Merged
    ORDER BY id_client, group_id, date_open;

    DROP Table Tmp_Merged;
    RENAME Table Tmp_Merged_New to Tmp_Merged;

    // 检查是否还有未合并的重叠区间
    Let vHasOverlap = 0;
    For i = 2 to NoOfRows('Tmp_Merged')
        Let vCurrOpen = Peek('date_open', i-1, 'Tmp_Merged');
        Let vPrevMaxClose = Peek('max_close', i-2, 'Tmp_Merged');
        Let vCurrClient = Peek('id_client', i-1, 'Tmp_Merged');
        Let vPrevClient = Peek('id_client', i-2, 'Tmp_Merged');
        If vCurrClient = vPrevClient and vCurrOpen <= vPrevMaxClose Then
            Let vHasOverlap = 1;
            Exit For;
        End If;
    Next i;
    If vHasOverlap = 0 Then
        Exit Do;
    End If;
Loop;

// 4. 生成最终合并结果
Final_Merged:
LOAD 
    id_client,
    min(date_open) as date_open,
    max(date_close) as date_close,
    concat(distinct id_question, ', ') as merged_question_ids
Resident Tmp_Merged
GROUP BY id_client, group_id;

DROP Tables Tmp_Sorted, Tmp_Merged;

脚本说明

日期修正：自动交换date_open > date_close的异常区间，确保所有区间的起始日期早于等于结束日期。
排序预处理：按客户和修正后的起始日期排序，为区间合并提供有序基础。
迭代合并：通过循环更新组内的最大结束日期，解决跨多行的区间重叠合并问题，确保所有相关区间被归入同一组。
结果聚合：按客户和组ID聚合，输出合并后的区间范围及对应的问题ID列表。

内容的提问来源于stack exchange，提问作者Alex

Qlik Sense中合并重叠日期字段的脚本问题及优化需求

问题背景 #

初始脚本 #

初始脚本问题 #

自行修改后的脚本 #

测试失败的数据集 #

解决方案 #

脚本说明 #