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:
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" 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:
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.
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
Oops it seems that I missed your reply. Anyway I found this blog very helpful: