Linking dates to different periods but with same name

[Christophe_DR]

Hi, I have two tables with test data. One containing information of incidents opened on a specific date and assigned to an ART (01...03). Second table contains information on release periods (format YYYYQ#_ART###), but start and stop date can be different for each ART. I need to report on release period (format '2020Q#', PI_NAME in script) which I derive via subfield function.

I want to link each incident to the proper release period. I tried with the IntervalMatch function, but I'm missing something I guess.

for example:

INC01 should be linked to 2020Q1_ART01

INC02 should be linked to 2020Q1_ART02

If I report on 2020Q1, I should see these two incidents

thx in advance

Incident table:

Incident	Date_open	ART_NAME
INC_01	28/03/2020	ART01
INC_02	10/02/2020	ART02
INC_03	20/07/2020	ART02
INC_04	15/05/2020	ART03
INC_05	19/08/2020	ART01
INC_06	17/09/2020	ART02
INC_07	3/04/2020	ART02
INC_08	27/03/2020	ART01
INC_09	7/04/2020	ART03
INC_10	19/03/2020	ART01

PI table:

PI_KEY	PI_START	PI_STOP
2020Q1_ART01	1/01/2020	31/03/2020
2020Q2_ART01	1/04/2020	30/06/2020
2020Q3_ART01	1/07/2020	30/09/2020
2020Q1_ART02	5/01/2020	29/03/2020
2020Q2_ART02	5/04/2020	25/06/2020
2020Q3_ART02	5/07/2020	26/09/2020
2020Q1_ART03	7/01/2020	30/03/2020
2020Q2_ART03	1/04/2020	16/06/2020
2020Q3_ART03	10/07/2020	29/09/2020

Current load script:

PI:
LOAD
PI_KEY,
subfield(PI_KEY, '_', 1) as PI_NAME,
subfield(PI_KEY, '_', 2) as PI_ART,
PI_START,
PI_STOP
FROM [lib://AttachedFiles/INC_PI_map.xlsx]
(ooxml, embedded labels, table is PI);

INC:
LOAD
Incident,
Date_open,
ART_NAME
FROM [lib://AttachedFiles/INC_PI_map.xlsx]
(ooxml, embedded labels, table is INC);

INC_MATCH:
IntervalMatch(Date_open)
Load
PI_START,
PI_STOP
FROM [lib://AttachedFiles/INC_PI_map.xlsx]
(ooxml, embedded labels, table is PI);

[Lauri]

Are you sure Qlik recognizes Date_open, PI_START, and PI_STOP as dates?

[Christophe_DR]

I'm pretty sure it does. The result of the load script gives me a table with three dates but I'm still missing the ART link. Also, I use the PI_START and PI_STOP in other reports and are correctly interpreted as dates.

Below the result when I run the load script