Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to link the following tables, but I need to look to the dates as well as the main link.
main:
Link | Start Date | End Date |
AA | 20/01/2009 | 19/01/2010 |
AA | 20/01/2010 | 19/01/2011 |
BB | 05/03/2012 | 06/03/2013 |
CC | 10/04/2010 | 09/04/2011 |
CC | 10/04/2011 | 09/04/2012 |
CC | 10/04/2012 | 09/04/2013 |
DD | 06/06/2011 | 05/06/2012 |
DD | 06/06/2012 | 05/06/2013 |
EE | 09/09/2011 | 08/09/2012 |
Other:
Link | Match Date | claim |
AA | 04/05/2009 | 123 |
BB | 18/06/2012 | 111 |
CC | 20/06/2011 | 234 |
EE | 02/01/2012 | 344 |
Ideally I would like to map the Start Date from the 'main' table to the 'Other' table. As you can see there can be multiple start and end dates attached to each 'Link', I only want to map the dates that the 'Match Date' falls between.
***
e.g.(only looking at AA)
Link | Start Date | End Date |
AA | 20/01/2009 | 19/01/2010 |
AA | 20/01/2010 | 19/01/2011 |
Link | Match Date | claim |
AA | 04/05/2009 | 123 |
The Match date falls between 20/01/2009 - 19/01/2010 , therefore this is the start date that should be mapped onto the 'Other' table.
***
The end result should look like this:
Link | Match Date | Start Date |
AA | 04/05/2009 | 20/01/2009 |
BB | 18/06/2012 | 05/03/2012 |
CC | 20/06/2011 | 10/04/2011 |
EE | 02/01/2012 | 09/09/2011 |
I have tried using Intervalmatch, but this only seems to look to the dates and doesn't look to the Link.
example is attached.
I would be grateful for your help.....
But it should be possible to build a combined key with dates and ID, and do interval match on this instead of just justing dates for interval match. Perhaps the ID needs to be numeric, can you use autonumberhas128 for the ID part and combine it with your dates? Please upload you excel file also if you need more help. // Robert
So no one can help?
Perhaps this will work:
main:
LOAD Link,
[Start Date],
[End Date],
Link & [Start Date] as StartKey,
Link & [End Date] as EndKey
FROM
(
Other:
LOAD Link,
[Match Date],
Link & [Match Date] as Key
FROM
(
INNER JOIN (Other) INTERVALMATCH (Key) LOAD StartKey, EndKey RESIDENT main;
LEFT JOIN (Other) LOAD * RESIDENT main;
DROP TABLE main;
Hi Robert,
Thank you for replying, but this hasn't worked!
looks like I'm going to have to think of a completely different approach to this dilemma..
But it should be possible to build a combined key with dates and ID, and do interval match on this instead of just justing dates for interval match. Perhaps the ID needs to be numeric, can you use autonumberhas128 for the ID part and combine it with your dates? Please upload you excel file also if you need more help. // Robert
Hi,
try this it may help you.
Thanks,
Niranjan M.
Hi Robert,
Thank you so much!!! this was my first time using autonumber and combined with your earlier suggestion; it seems to have done the trick!!
Here is how I did it:
main:
LOAD Link as Link1,
AutoNumber(Link) as autolink,
[Start Date],
[End Date]
FROM
(biff, embedded labels, table is Sheet1$);
Other:
LOAD Link as Link2,
AutoNumber(Link) as autolink,
[Match Date]
FROM
(biff, embedded labels, table is Sheet2$);
main1:
LOAD Link1 as LinkA,
autolink,
[Start Date],
[End Date],
autolink & [Start Date] as StartKey,
autolink & [End Date] as EndKey
resident main;
drop table main;
Other1:
LOAD Link2 as LinkB,
autolink,
[Match Date],
autolink & [Match Date] as Key
resident Other;
drop table Other;
INNER JOIN (Other1) INTERVALMATCH (Key) LOAD StartKey, EndKey RESIDENT main1;
LEFT JOIN (Other1) LOAD * RESIDENT main1;
DROP TABLE main1;
Niranjan - this didn't work for what I wanted, but thank you for your suggestion
Hi,
we can try above login by extended interval match. see below
Main:
Load
Link,
Date#(StartDate,'dd/mm/yyyy') As StartDate,
Date#(EndDate,'dd/mm/yyyy') As EndDate
;
LOAD * INLINE [
Link, StartDate, EndDate
AA, 20/01/2009, 19/01/2010
AA, 20/01/2010, 19/01/2011
BB, 05/03/2012, 06/03/2013
CC, 10/04/2010, 09/04/2011
CC, 10/04/2011, 09/04/2012
CC, 10/04/2012, 09/04/2013
DD, 06/06/2011, 05/06/2012
DD, 06/06/2012, 05/06/2013
EE, 09/09/2011, 08/09/2012
];
Other:
Load
Link as Link,
Date#(MatchDate,'dd/mm/yyyy') As MatchDate,
claim
;
LOAD * INLINE [
Link, MatchDate, claim
AA, 21/01/2009, 123
BB, 18/06/2012, 111
CC, 20/06/2011, 234
];
Inner Join(Other) IntervalMatch(MatchDate,Link) LOAD StartDate,EndDate,Link Resident Main;