topic Re: How do I join two tables based on timestamps that don't exactly match? in Discussions

How do I join two tables based on timestamps that don't exactly match?

user8421 — Sat, 10 Jun 2023 23:28:10 GMT

Hi everyone!

I am having an issue where I have Data Table 1 as shown below with a timestamp column and various columns of data

Timestamp	Column A	Column B
02/08/2021 11:45 AM	A1	B1
02/08/2021 1:39 PM	A2	B2
02/08/2021 11:37 PM	A3	B3
02/13/2021 12:01 AM	A4	B4
02/13/2021 4:00 PM	A5	B5
02/13/2021 10:22 PM	A6	B6
02/18/2021 9:00 AM	A7	B7
02/18/2021 12:01 PM	A8	B8
02/21/2021 9:15 AM	A9	B9
02/21/2021 11:50 AM	A10	B10
02/21/2021 3:00 PM	A11	B11

And Data Table 2 also with a timestamp column and corresponding columns of data

Timestamp	Column C	Column D
02/08/2021 11:24 AM	C1	D1
02/13/2021 12:07 AM	C2	D2
02/18/2021 10:29 AM	C3	D3
02/21/2021 11:16 AM	C4	D4

I would like to join these two data tables together where I preserve the timestamp column of Data Table 1, but join Columns C and D from Data Table 2 into Data Table 1 where the data is applied starting from the time indicated in the timestamp in Data Table 2. The resulting column would look like this:

Timestamp	Column A	Column B	Column C	Column D
02/08/2021 11:45 AM	A1	B1	C1	D1
02/08/2021 1:39 PM	A2	B2	C1	D1
02/08/2021 11:37 PM	A3	B3	C1	D1
02/13/2021 12:01 AM	A4	B4	C1	D1
02/13/2021 4:00 PM	A5	B5	C2	D2
02/13/2021 10:22 PM	A6	B6	C2	D2
02/18/2021 9:00 AM	A7	B7	C2	D2
02/18/2021 12:01 PM	A8	B8	C3	D3
02/21/2021 9:15 AM	A9	B9	C3	D3
02/21/2021 11:50 AM	A10	B10	C4	D4
02/21/2021 3:00 PM	A11	B11	C4	D4

Thanks in advance for any help!

Re: How do I join two tables based on timestamps that don't exactly match?

jthi — Fri, 02 Apr 2021 08:42:44 GMT

Maybe this could help or at least give idea what you could do:

"Fuzzymerge" - join tables with slightly different timestamps

Re: How do I join two tables based on timestamps that don't exactly match?

txnelson — Fri, 02 Apr 2021 09:39:14 GMT

Here is a script that will duplicate the matching you have in your example.

The actual working part of the script is less than 20 lines. For completeness, I have included the JSL that creates your original example data tables at the beginning of the script. That allows you to just cut and paste the script and it should run without error.

Names Default To Here( 1 );
dt = New Table( "Base",
	Add Rows( 11 ),
	New Column( "Timestamp",
		Numeric,
		"Continuous",
		Format( "m/d/y h:m", 19 ),
		Input Format( "m/d/y h:m" ),
		Set Values(
			[3695629500, 3695636340, 3695672220, 3696019260, 3696076800, 3696099720, 3696483600, 3696494460, 3696743700,
			3696753000, 3696764400]
		)
	),
	New Column( "Column A",
		Character( 16 ),
		"Nominal",
		Set Values( {"A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10", "A11"} )
	),
	New Column( "Column B",
		Character( 16 ),
		"Nominal",
		Set Values( {"B1", "B2", "B3", "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11"} )
	)
);
dt2 = New Table( "Match",
	Add Rows( 4 ),
	New Column( "Timestamp",
		Numeric,
		"Continuous",
		Format( "m/d/y h:m", 19 ),
		Input Format( "m/d/y h:m" ),
		Set Values( [3695628240, 3696019620, 3696488940, 3696750960] )
	),
	New Column( "Column C", Character( 16 ), "Nominal", Set Values( {"C1", "C2", "C3", "C4"} ) ),
	New Column( "Column D", Character( 16 ), "Nominal", Set Values( {"D1", "D2", "D3", "D4"} ) )
);

// Here is the actual matching code
dt << New Column("TheRow");
dt2 << New Column("TheRow", formula(Row()));

// Find the row in the Match data table that is to be paired with the row
// in the Base data table
For( i = 1, i <= N Rows( dt ), i++,
	dt:theRow[i] = Max( dt2 << get rows where( dt2:timestamp <= dt:Timestamp[i] ) )
);
// Update data tables
Data Table( "Base" ) << Update(
	With( Data Table( "Match" ) ),
	Match Columns( :TheRow = :TheRow )
);
// Delete column: TheRow
Data Table( "Base" ) << Delete Columns( :TheRow );
// Delete column: TheRow
Data Table( "Match" ) << Delete Columns( :TheRow );

Re: How do I join two tables based on timestamps that don't exactly match?

Dan_Obermiller — Fri, 02 Apr 2021 18:21:00 GMT

In your small example it looks like you are just matching based on date, not time. Could that possibly work instead?

EDIT: You can safely ignore this comment. I had looked too quickly at the example.

Re: How do I join two tables based on timestamps that don't exactly match?

txnelson — Fri, 02 Apr 2021 17:59:27 GMT

@Dan_Obermiller

2 of the rows do not match based upon date. The table matching as shown seems to be based upon prior date closest to target date.

Re: How do I join two tables based on timestamps that don't exactly match?

Dan_Obermiller — Fri, 02 Apr 2021 18:20:24 GMT

Of course you are right @txnelson. I just looked too quickly. I have edited my original reply.