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.
