1 Reply Latest reply: Nov 11, 2015 10:36 PM by Settu Periyasamy RSS

    Matching dates between different tables

      Hi everybody,

       

      I am trying to match dates between two different tables.

      I have one table with the following fields:

      Inv_dates:

       

      Part_Part,

      Inv_date

       

      and second table with :

       

      Cost_cal_date:

       

      Part ,

      Cost_cal_date

       

      The data in these tables is , as an example :

       

      1:

      231/1/2013
      781/2/2013
      7825/02/2013
      883/3/2013
      885/3/2013

      2:

         

      2331/12/2012
      2331/01/2013
      2328/02/2013
      2331/03/2013
      2330/04/2013
      2331/05/2013
      7831/12/2012
      7831/01/2013
      7828/02/2013
      7831/03/2013
      7830/04/2013
      7831/05/2013
      8831/12/2012
      8831/01/2013
      8828/02/2013
      8831/03/2013
      8830/04/2013
      8831/05/2013

       

      What I am trying to do is to on the first table, for each line, the most close COST_CAL_DATE from the second table but which is earlier than the date on the line. for example

       

          

      231/1/201331/12/2012
      781/2/201331/01/2013
      7825/02/201331/01/2013
      883/3/201328/02/2013
      885/3/201328/02/2013

       

       

      I have tried to first join both tables, with left join and than to read from the same table for a new one with

      'where max(COST_CAL_DATE) <= INV_DATE' but it didn't work.

       

      I also tried to work with the INTERVALMATCH function but it didn't work.

       

      I will appreciate any help.

       

      Thank You,

       

      Osher Sassoni.

        • Re: Matching dates between different tables
          Settu Periyasamy

          Hi,

          Try the below script..

          Inv:
          LOAD * INLINE [
              Part, Inv_dates
              23, 1/1/2013
              78, 2/1/2013
              78, 2/25/2013
              88, 3/3/2013
              88, 3/5/2013
          ];
          Left Join(Inv)
          Cost:
          LOAD * INLINE [
              Part, Cost_cal_date
              23, 12/31/2012
              23, 1/31/2013
              23, 2/28/2013
              23, 3/31/2013
              23, 4/30/2013
              23, 5/31/2013
              78, 12/31/2012
              78, 1/31/2013
              78, 2/28/2013
              78, 3/31/2013
              78, 4/30/2013
              78, 5/31/2013
              88, 12/31/2012
              88, 1/31/2013
              88, 2/28/2013
              88, 3/31/2013
              88, 4/30/2013
              88, 5/31/2013
          ];
          NoConcatenate
          New:
          LOAD   Part, 
            Inv_dates,
            Max(Cost_cal_date) as Cost_cal_date 
          Resident Inv Where Cost_cal_date<=Inv_dates Group by Part,Inv_dates;
          
          DROP Table Inv;
          

           

          Capture.JPG