1 Reply Latest reply: Jan 19, 2015 7:06 AM by Christian Wolf RSS

    Connecting tables using a date range

      Hi,

       

      I have two tables

      One with each client ID and the date a sale took place

       

      Client IDSale Date
      1001/1/14
      1012/2/14
      1005/7/14

       

      I would like to connect it to the following table of documents, based on a date range of 30 days before and 90 days after

      I can do this in an expression, but cannot work out how to do in during LOAD

      If(Document_Note_Date-Sale_Date>=-30 and Document_Note_Date-Sale_Date<=90,Sale_Date)

       

      Client IDDocument Note Date
      10020/12/13
      1007/1/14
      1001/7/14
      10120/02/14

       

      Then I can connect the tables using a combination of Client ID + Sale Date

       

      Any help would be greatly appreciated.

        • Re: Connecting tables using a date range
          Christian Wolf

          Hi George,

           

          you can solve your problem with the Intervalmatch Function:

           

          TAB1a:
          LOAD
          ClientID & '|'& SaleDate as %ID,
          *,
          SaleDate-30 as Start,
          SaleDate+90 as End
          Resident TAB1;
          
          
          Drop Table TAB1;
          
          
          left Join(TAB2)
          IntervalMatch(DocNoteDate)
          LOAD
          Start,
          End
          Resident TAB1a;
          
          
          Left Join(TAB2)
          LOAD
          ClientID,
          Start,
          End,
          %ID
          Resident TAB1a;
          
          
          DROP Field ClientID From TAB2;
          DROP Fields Start,End;
          
          

           

          Regards

          Christian