Table join on ID and nearest date

[Anonymous]

Hi,

I have 2 tables that I need to join and create 1. They need to join on their ID's and dates where date1 from table1 is the nearest date before date2 table2.

Table1

ID	Date1	Data1
1	16/04/2022	1
1	19/02/2022	2
2	25/04/2022	3
3	30/04/2022	4

Table2

ID	Date2	Data2
1	18/04/2022	a
1	21/02/2022	b
2	27/04/2022	c

Final Table

ID	Date1	Data1	Date2	Data2
1	16/04/2022	1	18/04/2022	a
1	19/02/2022	2	21/02/2022	b
2	25/04/2022	3	27/04/2022	c
3	30/04/2022	4

[vinieme12]

Hi @Anonymous , please post some same data in excel/csv this provides more information on granularity of two data sets and the type of relations 1-1 , 1-n etc

[Anonymous]

Hi @vinieme12  thanks for helping out. i have edited the original post with an example.

it would seem intervalmatch is kind of what i am after, but i dont have a date range....

[vinieme12]

here you go

table1:
LOAD ID,Data1,date#(Date1,'DD/MM/YYYY') as Date1 Inline [
ID,Date1,Data1
1,16/04/2022,1
1,19/02/2022,2
2,25/04/2022,3
3,30/04/2022,4
];

NoConcatenate
table1_:
Load
ID as ID1
,Date1
,Data1
,ID&'-'&autonumber(ID&Date1,'table1_') as auto_id
Resident table1
Order by ID,Date1 asc;
Drop table table1;

table2:
LOAD ID,Data2,date#(Date2,'DD/MM/YYYY') as Date2 Inline [
ID,Date2,Data2
1,18/04/2022,a
1,21/02/2022,b
2,27/04/2022,c
];

NoConcatenate
table2_:
Load
ID as ID2
,Date2
,Data2
,ID&'-'&autonumber(ID&Date2,'table2_') as auto_id
Resident table2
Order by ID,Date2 asc;
Drop table table2;

exit Script;

[Anonymous]

Wow, how quick was that! you are amazing! what a great solution.

The only issue I have is if there are multiple records in table1 with the same ID, the next record in table2 with a matching ID will be linked irrespective of the date.

table1:
LOAD ID,Data1,date#(Date1,'DD/MM/YYYY') as Date1 Inline [
ID,Date1,Data1
1,16/04/2022,1
1,19/02/2022,2
2,25/04/2022,3
3,30/04/2022,4
3,01/05/2022,5
];

NoConcatenate
table1_:
Load
ID as ID1
,Date1
,Data1
,ID&'-'&autonumber(ID&Date1,'table1_') as auto_id
Resident table1
Order by ID,Date1 asc;
Drop table table1;

table2:
LOAD ID,Data2,date#(Date2,'DD/MM/YYYY') as Date2 Inline [
ID,Date2,Data2
1,18/04/2022,a
1,21/02/2022,b
2,27/04/2022,c
3,02/05/2022,d
];

NoConcatenate
table2_:
Load
ID as ID2
,Date2
,Data2
,ID&'-'&autonumber(ID&Date2,'table2_') as auto_id
Resident table2
Order by ID,Date2 asc;
Drop table table2;

exit Script;

[vinieme12]

yes, there is simply no way to check a condition like table1.date < table2.date2  as a nearest date  for each ID as there could be multiple dates that satisfy this criteria