Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
chaorenzhu
Creator
Creator

Compare if two date intervals overlap in two tables

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:

iddate1date2
0012018-04-202019-04-19
0022018-04-202019-04-19
0012019-04-202020-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:

iddate3type
0012018-04-07start
0012019-02-09end
0012020-09-17start
0012021-01-01end
0022017-08-01start
0022019-03-24end

 

"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:

iddate1date2flag
0012018-04-202019-04-191
0022018-04-202019-04-191
0012019-04-202020-04-190

 

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. 

Labels (3)
4 Replies
Marcos_Ferreira_dos_Santos

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

chaorenzhu
Creator
Creator
Author

Hi Marcos,

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

Marcos_Ferreira_dos_Santos

Hi chaorenzhu,

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

Best regards,

Marcos

 

 

chaorenzhu
Creator
Creator
Author

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