Help with match with last date from another table

[Vinerc]

Hello all,

I'm trying to do something like approximate match with dates and can't figure out how. I have two separate tables:

Failures:

Vehicle	Date
1	01/03/2019
1	01/07/2019
2	12/11/2019
3	01/04/2019

Maintenances:

Vehicle	Date
1	14/12/2018
1	16/04/2019
1	17/08/2019
1	18/12/2019
2	01/01/2019
2	01/06/2019
2	01/11/2019
2	01/04/2020
3	01/02/2019
3	01/05/2019
3	01/08/2019
3	01/11/2019
3	01/02/2020

I want to create a column in failures to pick the last maintenance of that vehicle before that failure, so the final table would be:

Failures:

Vehicle	Date	Last Maintenance
1	01/03/2019	14/12/2018
1	01/07/2019	16/04/2019
2	12/11/2019	01/11/2019
3	01/04/2019	01/02/2019

Thanks in advance.

[lironbaram · Accepted solution]

hi 

using interval match function you could do it 

data:
load * inline [
Vehicle, Date
1 ,01/03/2019
1 ,01/07/2019
2,12/11/2019
3 ,01/04/2019
];

MaintenancesTemp:
NoConcatenate load * inline [
Vehicle ,Date
1, 14/12/2018
1 ,16/04/2019
1 ,17/08/2019
1, 18/12/2019
2 ,01/01/2019
2, 01/06/2019
2 ,01/11/2019
2, 01/04/2020
3 ,01/02/2019
3, 01/05/2019
3 ,01/08/2019
3, 01/11/2019
3 ,01/02/2020
];

maintenance:

load Vehicle,
Date as StartDate,
if (isnull(Previous(Date)),today(),Previous(Date)) as Enddate
resident MaintenancesTemp
order by Vehicle,Date Desc;

drop Table MaintenancesTemp;

inner join IntervalMatch(Date,Vehicle) load StartDate,Enddate ,Vehicle Resident maintenance;

[Vinerc]

Thanks for the reply Lironbaram,

I have just changed one line, from:

if (isnull(Previous(Date)),today(),Previous(Date)) as Enddate

To:

if (isnull(Previous(Date)) or Previous(Vehicle)<>Vehicle ,today(),Previous(Date)) as Enddate

Because the previous date was not null and it solved the problem.

Thanks for the help! I was having a hard time figuring out. Just one question, it created a synthetic key, is there any problem with that or I can leave it with that syn key?

[Vinerc]

Just for the record, I solved the problem adding this piece of code:

data_new:
LOAD
Vehicle & Date as %ID,
Date
resident data;

Left Join

maintenance_new:
LOAD
Vehicle & Date as %ID,
StartDate
resident maintenance;

DROP Tables maintenance,data;


It's probably not the best way but I needed just one table to do some operations.