7 Replies Latest reply: Sep 6, 2012 7:23 AM by niranjan RSS

    Intervalmatch or Map - Please Help

      Hi,

       

      I'm trying to link the following tables, but I need to look to the dates as well as the main link.

      main:                                          

      LinkStart DateEnd Date
      AA20/01/200919/01/2010
      AA20/01/201019/01/2011
      BB05/03/201206/03/2013
      CC10/04/201009/04/2011
      CC10/04/201109/04/2012
      CC10/04/201209/04/2013
      DD06/06/201105/06/2012
      DD06/06/201205/06/2013
      EE09/09/201108/09/2012

      Other:

      LinkMatch Dateclaim
      AA04/05/2009123
      BB18/06/2012111
      CC20/06/2011234
      EE02/01/2012344

       

      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)

      LinkStart DateEnd Date
      AA20/01/200919/01/2010
      AA20/01/201019/01/2011

       

      LinkMatch Dateclaim
      AA04/05/2009123

      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:

      LinkMatch DateStart Date
      AA04/05/200920/01/2009
      BB18/06/201205/03/2012
      CC20/06/201110/04/2011
      EE02/01/201209/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.....

        • Re: Intervalmatch or Map - Please Help

          So no one can help?

          • Re: Intervalmatch or Map - Please Help
            Robert Svebeck

            Perhaps this will work:

             

            main:

             

             

            LOAD Link,
            [Start Date],

                
            [End Date],

                
            Link & [Start Date] as StartKey,

                
            Link & [End Date] as EndKey

            FROM

            [X:\+TUT Wholesale\Pricing\testing.xls]

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









            Other:

            LOAD Link,

                
            [Match Date],

                
            Link & [Match Date] as Key

            FROM

            [X:\+TUT Wholesale\Pricing\testing.xls]

            (
            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;

             

              • Re: Intervalmatch or Map - Please Help

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

                  • Re: Intervalmatch or Map - Please Help
                    Robert Svebeck

                    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

                      • Re: Intervalmatch or Map - Please Help

                        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
                        [X:\+TUT Wholesale\Pricing\GIT\testing.xls]
                        (biff, embedded labels, table is Sheet1$);

                         

                        Other:
                        LOAD Link as Link2,
                             AutoNumber(Link) as autolink,
                             [Match Date]
                        FROM
                        [X:\+TUT Wholesale\Pricing\GIT\testing.xls]
                        (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


                          • Re: Intervalmatch or Map - Please Help

                            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;

                             

                    • Re: Intervalmatch or Map - Please Help

                      Hi,

                       

                      try this it may help you.

                       

                      Thanks,

                      Niranjan M.