topic Re: Compare if two date intervals overlap in two tables in QlikView

Compare if two date intervals overlap in two tables

chaorenzhu — Wed, 26 May 2021 15:14:38 GMT

Hi,

I'm new to Qlik, and in my scenario I have two tables, table1 and table2. In table1 there are two date columns, date1 and date2, where date1 < date2. Example as below:

id	date1	date2
001	2018-04-20	2019-04-19
002	2018-04-20	2019-04-19
001	2019-04-20	2020-04-19

In table 2 there is a single date column, date3. There is another column "type", with two values 'start' and 'end', that are linked to date3, and date3 when type='start'< date3 when type='end'. Essentially there are another set of date intervals in table2. An example as below:

id	date3	type
001	2018-04-07	start
001	2019-02-09	end
001	2020-09-17	start
001	2021-01-01	end
002	2017-08-01	start
002	2019-03-24	end

"id" is the primary key for both table1 and table2. What I'm hoping to achieve is to add a column "flag" in table1, if the date interval in table1 overlaps with any date interval in table2 then value as 1, else value as 0. Example as below:

id	date1	date2	flag
001	2018-04-20	2019-04-19	1
002	2018-04-20	2019-04-19	1
001	2019-04-20	2020-04-19	0

I'm stuck at how to implement this for quite a while. I mainly have two questions.

1) I'm thinking of reshaping table2 to split date3 into two columns, date_start and date_end. In Python I can do that using unstack() but not sure how to do that in Qlik? Note that for each id, there could be more than 2 records of date3, i.e. more than 1 pair of date_start and date_end.

2)How to loop through table2 and find if there is any overlapping between the date interval in table1 and table2?

Your help is greatly appreciated! Thanks.

Re: Compare if two date intervals overlap in two tables

Marcos_Ferreira_dos_Santos — Fri, 28 May 2021 16:34:42 GMT

Hi chaorenzhu,

Yes, you can manage this in Qlikview, with some programming in script and two wonderful Qlikview tools:

1) you can implement a "preceding load" logic to achieve it

2) you can use "intervalmatch" function to do it

Good luck

Marcos

Re: Compare if two date intervals overlap in two tables

chaorenzhu — Mon, 31 May 2021 09:28:25 GMT

Hi Marcos,

Thanks for your advice! Would you mind providing some script on how to implement this? Thank you.

Re: Compare if two date intervals overlap in two tables

Marcos_Ferreira_dos_Santos — Mon, 07 Jun 2021 18:56:07 GMT

Hi chaorenzhu,

I don't have these peaces of script with me now. Have you solved your issues ? I hope so.

Best regards,

Marcos

Re: Compare if two date intervals overlap in two tables

chaorenzhu — Fri, 25 Jun 2021 01:53:48 GMT

Oops it seems that I missed your reply. Anyway I found this blog very helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578