Intervalmatch or Map - Please Help

[#1]

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.....

[#2]

So no one can help?

[RSvebeck]

Perhaps this will work:

main:

LOAD Link,
[Start Date],

     [End Date],

     Link & [Start Date] as StartKey,

     Link & [End Date] as EndKey

FROM



(biff, embedded labels, table is Sheet1$);









Other:

LOAD Link,

     [Match Date],

     Link & [Match Date] as Key

FROM



(biff, embedded labels, table is Sheet2$);







INNER JOIN (Other) INTERVALMATCH (Key) LOAD StartKey, EndKey RESIDENT main;

LEFT JOIN (Other) LOAD * RESIDENT main;

DROP TABLE main;

[#4]

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..

[RSvebeck · Accepted solution]

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

[#6]

Hi,

try this it may help you.

Thanks,

Niranjan M.

[#7]

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

[#8]

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;