join Intermatch 2 dates

anwarbham — Fri, 15 Nov 2024 23:40:13 GMT

Hi im having difficulty joining 2 tables based on start date and end date

i have 2 tables

the Account Table that has a unique consent ID where the key is Account_Key

and the Cycle Table where there is also Account_Key
both tables have a start date and end date. or capture date and expiration date

i need to find out which Consent IDs from the Account Table , can be linked to which cycles

i can use a join Account -Account and then the capture date with intermatch to start date and end date.

but i also want to include the records that fall before the expiration date, how can i achieve this

Re: join Intermatch 2 dates

edwin — Sat, 07 Nov 2020 02:05:44 GMT

on high level, you need to relate the two tables row by row where the AccountKey are the same and the dates overlap. to do this:
1. create a link table which is an outer join of the two tables on AccountKey; include keys for each table + the dates
2. then retain only the rows where the dates overlap
3. drop the AccountKey and the dates as you only need the keys to link back to the two tables.

Obviously, AccountKey needs to have different names in the original tables. the relationship will look like this:

here is the sample code:

table1: load %consentKey, %AccountKey, date(today()+offset1) as capDate, date(today()+offset2) as expDate inline [ %consentKey, %AccountKey,offset1, offset2 z0001, 1, -5, 5 z0002, 2, -2, 4 ]; NoConcatenate table2: load %cycleKey, %AccountKey2, date(today()+offset1) as startDate, date(today()+offset2) as endDate inline [ %cycleKey, %AccountKey2, offset1, offset2 x0001, 1, -2, 3 x0002, 1, 6, 9 x0003, 2, -10, 1 x0004, 2, -10, -9 ]; NoConcatenate bridge1: load distinct %AccountKey, %consentKey, capDate as capDate2, expDate as expDate2 resident table1; inner join (bridge1) load distinct %AccountKey2 as %AccountKey, %cycleKey, startDate as startDate2, endDate as endDate2 resident table2; NoConcatenate bridge: load %consentKey, %cycleKey Resident bridge1 where capDate2>=startDate2 and expDate2 >=endDate2 and endDate2>=capDate2 or capDate2<=startDate2 and expDate2 <=endDate2 and expDate2>=startDate2 or capDate2 >=startDate2 and endDate2 >=expDate2 or startDate2 >=capDate2 and expDate2>=endDate2; drop table bridge1;

so only the ones that overlap will be associated:

disclaimer: not sure if ive captured all the test cases for the overlapping dates - you need to add on to the where clause if there are other test cases.

topic Re: join Intermatch 2 dates in QlikView

join Intermatch 2 dates

Re: join Intermatch 2 dates