Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
23 | 1/1/2013 |
78 | 1/2/2013 |
78 | 25/02/2013 |
88 | 3/3/2013 |
88 | 5/3/2013 |
2:
23 | 31/12/2012 |
23 | 31/01/2013 |
23 | 28/02/2013 |
23 | 31/03/2013 |
23 | 30/04/2013 |
23 | 31/05/2013 |
78 | 31/12/2012 |
78 | 31/01/2013 |
78 | 28/02/2013 |
78 | 31/03/2013 |
78 | 30/04/2013 |
78 | 31/05/2013 |
88 | 31/12/2012 |
88 | 31/01/2013 |
88 | 28/02/2013 |
88 | 31/03/2013 |
88 | 30/04/2013 |
88 | 31/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
23 | 1/1/2013 | 31/12/2012 |
78 | 1/2/2013 | 31/01/2013 |
78 | 25/02/2013 | 31/01/2013 |
88 | 3/3/2013 | 28/02/2013 |
88 | 5/3/2013 | 28/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.
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;