Hello all, I have 2 tables with various date times and values. These dates times don't match up exactly. What I'm trying to do is to do a join on the date times of both columns, if table 2 contains a time between a given span. An example below: table 1: Date TimeValue10/18/2022 17:22110/18/2022 20:41210/19/2022 0:36310/19/2022 2:32410/19/2022 4:37510/19/2022 6:33610/19/2022 7:00710/19/2022 10:00810/19/2022 10:04910/19/2022 22:001010/20/2022 2:001110/20/2022 6:271210/20/2022 10:001310/20/2022 14:001410/20/2022 21:071510/21/2022 10:001610/21/2022 14:001710/21/2022 19:001810/21/2022 22:0019 table 2 DattimeValue2SpanSpanBackSpanForward10/18/2022 4:001210/18/2022 2:0010/18/2022 6:0010/18/2022 5:002210/18/2022 3:0010/18/2022 7:0010/18/2022 6:003210/18/2022 4:0010/18/2022 8:0010/18/2022 7:004210/18/2022 5:0010/18/2022 9:0010/18/2022 8:005210/18/2022 6:0010/18/2022 10:0010/18/2022 9:006210/18/2022 7:0010/18/2022 11:0010/18/2022 10:007210/18/2022 8:0010/18/2022 12:0010/18/2022 11:008210/18/2022 9:0010/18/2022 13:0010/18/2022 12:009210/18/2022 10:0010/18/2022 14:0010/18/2022 13:0010210/18/2022 11:0010/18/2022 15:0010/18/2022 14:0011210/18/2022 12:0010/18/2022 16:0010/18/2022 15:0012210/18/2022 13:0010/18/2022 17:0010/18/2022 16:0013210/18/2022 14:0010/18/2022 18:0010/18/2022 17:0014210/18/2022 15:0010/18/2022 19:0010/18/2022 18:0015210/18/2022 16:0010/18/2022 20:0010/18/2022 19:0016210/18/2022 17:0010/18/2022 21:0010/18/2022 20:0017210/18/2022 18:0010/18/2022 22:0010/18/2022 21:0018210/18/2022 19:0010/18/2022 23:0010/18/2022 22:0019210/18/2022 20:0010/19/2022 0:0010/18/2022 23:0020210/18/2022 21:0010/19/2022 1:0010/19/202221210/18/2022 22:0010/19/2022 2:0010/19/2022 1:0022210/18/2022 23:0010/19/2022 3:0010/19/2022 2:0023210/19/2022 0:0010/19/2022 4:0010/19/2022 3:0024210/19/2022 1:0010/19/2022 5:0010/19/2022 4:0025210/19/2022 2:0010/19/2022 6:0010/19/2022 5:0026210/19/2022 3:0010/19/2022 7:0010/19/2022 8:0027210/19/2022 6:0010/19/2022 10:0010/19/2022 9:0028210/19/2022 7:0010/19/2022 11:0010/19/2022 10:0029210/19/2022 8:0010/19/2022 12:0010/19/2022 11:0030210/19/2022 9:0010/19/2022 13:0010/19/2022 12:0031210/19/2022 10:0010/19/2022 14:0010/19/2022 13:0032210/19/2022 11:0010/19/2022 15:0010/19/2022 14:0033210/19/2022 12:0010/19/2022 16:0010/19/2022 15:0034210/19/2022 13:0010/19/2022 17:0010/19/2022 20:0035210/19/2022 18:0010/19/2022 22:0010/19/2022 21:0036210/19/2022 19:0010/19/2022 23:0010/19/2022 22:0037210/19/2022 20:0010/20/2022 0:0010/19/2022 23:0038210/19/2022 21:0010/20/2022 1:0010/20/202239210/19/2022 22:0010/20/2022 2:0010/20/2022 1:0040210/19/2022 23:0010/20/2022 3:0010/20/2022 2:0041210/20/2022 0:0010/20/2022 4:0010/20/2022 3:0042210/20/2022 1:0010/20/2022 5:0010/20/2022 4:0043210/20/2022 2:0010/20/2022 6:0010/20/2022 5:0044210/20/2022 3:0010/20/2022 7:0010/20/2022 6:0045210/20/2022 4:0010/20/2022 8:0010/20/2022 7:0046210/20/2022 5:0010/20/2022 9:0010/20/2022 8:0047210/20/2022 6:0010/20/2022 10:0010/20/2022 9:0048210/20/2022 7:0010/20/2022 11:0010/20/2022 10:0049210/20/2022 8:0010/20/2022 12:0010/20/2022 11:0050210/20/2022 9:0010/20/2022 13:0010/20/2022 12:0051210/20/2022 10:0010/20/2022 14:0010/20/2022 13:0052210/20/2022 11:0010/20/2022 15:0010/20/2022 14:0053210/20/2022 12:0010/20/2022 16:0010/20/2022 15:0054210/20/2022 13:0010/20/2022 17:0010/20/2022 21:0055210/20/2022 19:0010/20/2022 23:0010/21/2022 12:0056210/21/2022 10:0010/21/2022 14:0010/21/2022 13:0057210/21/2022 11:0010/21/2022 15:0010/21/2022 14:0058210/21/2022 12:0010/21/2022 16:0010/21/2022 15:0059210/21/2022 13:0010/21/2022 17:0010/21/2022 16:0060210/21/2022 14:0010/21/2022 18:0010/21/2022 17:0061210/21/2022 15:0010/21/2022 19:0010/21/2022 18:0062210/21/2022 16:0010/21/2022 20:0010/21/2022 19:0063210/21/2022 17:0010/21/2022 21:0010/21/2022 20:0064210/21/2022 18:0010/21/2022 22:0010/21/2022 21:0065210/21/2022 19:0010/21/2022 23:0010/21/2022 22:0066210/21/2022 20:0010/22/2022 0:0010/21/2022 23:0067210/21/2022 21:0010/22/2022 1:00 Result of joining: Date TimeValueValue2Explanation10/18/2022 17:22114.514 1510/18/2022 20:41217.517 1810/19/2022 0:36321.521 2210/19/2022 2:32423.523 2410/19/2022 4:37525.525 2610/19/2022 6:336*None10/19/2022 7:007272710/19/2022 10:0082928 29 3010/19/2022 10:04929.529 3010/19/2022 22:00103736 27 3810/20/2022 2:001141.541 4210/20/2022 6:271245.545 4610/20/2022 10:001349.549 5010/20/2022 14:00145352 53 5410/20/2022 21:0715555510/21/2022 10:0016*None10/21/2022 14:00175857 58 5910/21/2022 19:00186362 63 6410/21/2022 22:00196665 66 67 The last column of the resulting join table is to show the logic. I currently have the work flow to input both tables, and for table 2, I have already been able to have an offset of my time and the span. Currently I'm stuck on this part.

Trying to Join data with matching critera

Hello all,

I have 2 tables with various date times and values. These dates times don't match up exactly. What I'm trying to do is to do a join on the date times of both columns, if table 2 contains a time between a given span. An example below:

table 1:

Date Time	Value
10/18/2022 17:22	1
10/18/2022 20:41	2
10/19/2022 0:36	3
10/19/2022 2:32	4
10/19/2022 4:37	5
10/19/2022 6:33	6
10/19/2022 7:00	7
10/19/2022 10:00	8
10/19/2022 10:04	9
10/19/2022 22:00	10
10/20/2022 2:00	11
10/20/2022 6:27	12
10/20/2022 10:00	13
10/20/2022 14:00	14
10/20/2022 21:07	15
10/21/2022 10:00	16
10/21/2022 14:00	17
10/21/2022 19:00	18
10/21/2022 22:00	19

table 2

Dattime	Value2	Span	SpanBack	SpanForward
10/18/2022 4:00	1	2	10/18/2022 2:00	10/18/2022 6:00
10/18/2022 5:00	2	2	10/18/2022 3:00	10/18/2022 7:00
10/18/2022 6:00	3	2	10/18/2022 4:00	10/18/2022 8:00
10/18/2022 7:00	4	2	10/18/2022 5:00	10/18/2022 9:00
10/18/2022 8:00	5	2	10/18/2022 6:00	10/18/2022 10:00
10/18/2022 9:00	6	2	10/18/2022 7:00	10/18/2022 11:00
10/18/2022 10:00	7	2	10/18/2022 8:00	10/18/2022 12:00
10/18/2022 11:00	8	2	10/18/2022 9:00	10/18/2022 13:00
10/18/2022 12:00	9	2	10/18/2022 10:00	10/18/2022 14:00
10/18/2022 13:00	10	2	10/18/2022 11:00	10/18/2022 15:00
10/18/2022 14:00	11	2	10/18/2022 12:00	10/18/2022 16:00
10/18/2022 15:00	12	2	10/18/2022 13:00	10/18/2022 17:00
10/18/2022 16:00	13	2	10/18/2022 14:00	10/18/2022 18:00
10/18/2022 17:00	14	2	10/18/2022 15:00	10/18/2022 19:00
10/18/2022 18:00	15	2	10/18/2022 16:00	10/18/2022 20:00
10/18/2022 19:00	16	2	10/18/2022 17:00	10/18/2022 21:00
10/18/2022 20:00	17	2	10/18/2022 18:00	10/18/2022 22:00
10/18/2022 21:00	18	2	10/18/2022 19:00	10/18/2022 23:00
10/18/2022 22:00	19	2	10/18/2022 20:00	10/19/2022 0:00
10/18/2022 23:00	20	2	10/18/2022 21:00	10/19/2022 1:00
10/19/2022	21	2	10/18/2022 22:00	10/19/2022 2:00
10/19/2022 1:00	22	2	10/18/2022 23:00	10/19/2022 3:00
10/19/2022 2:00	23	2	10/19/2022 0:00	10/19/2022 4:00
10/19/2022 3:00	24	2	10/19/2022 1:00	10/19/2022 5:00
10/19/2022 4:00	25	2	10/19/2022 2:00	10/19/2022 6:00
10/19/2022 5:00	26	2	10/19/2022 3:00	10/19/2022 7:00
10/19/2022 8:00	27	2	10/19/2022 6:00	10/19/2022 10:00
10/19/2022 9:00	28	2	10/19/2022 7:00	10/19/2022 11:00
10/19/2022 10:00	29	2	10/19/2022 8:00	10/19/2022 12:00
10/19/2022 11:00	30	2	10/19/2022 9:00	10/19/2022 13:00
10/19/2022 12:00	31	2	10/19/2022 10:00	10/19/2022 14:00
10/19/2022 13:00	32	2	10/19/2022 11:00	10/19/2022 15:00
10/19/2022 14:00	33	2	10/19/2022 12:00	10/19/2022 16:00
10/19/2022 15:00	34	2	10/19/2022 13:00	10/19/2022 17:00
10/19/2022 20:00	35	2	10/19/2022 18:00	10/19/2022 22:00
10/19/2022 21:00	36	2	10/19/2022 19:00	10/19/2022 23:00
10/19/2022 22:00	37	2	10/19/2022 20:00	10/20/2022 0:00
10/19/2022 23:00	38	2	10/19/2022 21:00	10/20/2022 1:00
10/20/2022	39	2	10/19/2022 22:00	10/20/2022 2:00
10/20/2022 1:00	40	2	10/19/2022 23:00	10/20/2022 3:00
10/20/2022 2:00	41	2	10/20/2022 0:00	10/20/2022 4:00
10/20/2022 3:00	42	2	10/20/2022 1:00	10/20/2022 5:00
10/20/2022 4:00	43	2	10/20/2022 2:00	10/20/2022 6:00
10/20/2022 5:00	44	2	10/20/2022 3:00	10/20/2022 7:00
10/20/2022 6:00	45	2	10/20/2022 4:00	10/20/2022 8:00
10/20/2022 7:00	46	2	10/20/2022 5:00	10/20/2022 9:00
10/20/2022 8:00	47	2	10/20/2022 6:00	10/20/2022 10:00
10/20/2022 9:00	48	2	10/20/2022 7:00	10/20/2022 11:00
10/20/2022 10:00	49	2	10/20/2022 8:00	10/20/2022 12:00
10/20/2022 11:00	50	2	10/20/2022 9:00	10/20/2022 13:00
10/20/2022 12:00	51	2	10/20/2022 10:00	10/20/2022 14:00
10/20/2022 13:00	52	2	10/20/2022 11:00	10/20/2022 15:00
10/20/2022 14:00	53	2	10/20/2022 12:00	10/20/2022 16:00
10/20/2022 15:00	54	2	10/20/2022 13:00	10/20/2022 17:00
10/20/2022 21:00	55	2	10/20/2022 19:00	10/20/2022 23:00
10/21/2022 12:00	56	2	10/21/2022 10:00	10/21/2022 14:00
10/21/2022 13:00	57	2	10/21/2022 11:00	10/21/2022 15:00
10/21/2022 14:00	58	2	10/21/2022 12:00	10/21/2022 16:00
10/21/2022 15:00	59	2	10/21/2022 13:00	10/21/2022 17:00
10/21/2022 16:00	60	2	10/21/2022 14:00	10/21/2022 18:00
10/21/2022 17:00	61	2	10/21/2022 15:00	10/21/2022 19:00
10/21/2022 18:00	62	2	10/21/2022 16:00	10/21/2022 20:00
10/21/2022 19:00	63	2	10/21/2022 17:00	10/21/2022 21:00
10/21/2022 20:00	64	2	10/21/2022 18:00	10/21/2022 22:00
10/21/2022 21:00	65	2	10/21/2022 19:00	10/21/2022 23:00
10/21/2022 22:00	66	2	10/21/2022 20:00	10/22/2022 0:00
10/21/2022 23:00	67	2	10/21/2022 21:00	10/22/2022 1:00

Result of joining:

Date Time	Value	Value2	Explanation
10/18/2022 17:22	1	14.5	14 15
10/18/2022 20:41	2	17.5	17 18
10/19/2022 0:36	3	21.5	21 22
10/19/2022 2:32	4	23.5	23 24
10/19/2022 4:37	5	25.5	25 26
10/19/2022 6:33	6	*	None
10/19/2022 7:00	7	27	27
10/19/2022 10:00	8	29	28 29 30
10/19/2022 10:04	9	29.5	29 30
10/19/2022 22:00	10	37	36 27 38
10/20/2022 2:00	11	41.5	41 42
10/20/2022 6:27	12	45.5	45 46
10/20/2022 10:00	13	49.5	49 50
10/20/2022 14:00	14	53	52 53 54
10/20/2022 21:07	15	55	55
10/21/2022 10:00	16	*	None
10/21/2022 14:00	17	58	57 58 59
10/21/2022 19:00	18	63	62 63 64
10/21/2022 22:00	19	66	65 66 67

The last column of the resulting join table is to show the logic.

I currently have the work flow to input both tables, and for table 2, I have already been able to have an offset of my time and the span. Currently I'm stuck on this part.

Join

Accepted answers

RobertOdera

Hi, @IMTran

Here you go.

Your outcome requires profound logic, and I hope the comments in the workflow are helpful.

Please see the below.

Please mark it as an acceptable solution if it works for you.

Cheers!

For_IMTran.yxzp

All comments

RobertOdera

Hi, @IMTran

Here you go.

Your outcome requires profound logic, and I hope the comments in the workflow are helpful.

Please see the below.

Please mark it as an acceptable solution if it works for you.

Cheers!

For_IMTran.yxzp

IMTran

Wow Robert,

Amazing work! Thank you so much.

Forgive me, ad you didn't need to do the value 2 explanation, I just wanted to show how the value were derived.

I tried adding more columns to table 2, but I got an error.

Is it possible for table 2 to merge multiple columns so that you'll have.

Datetime, Value,Value2, value3, value4, etc?

IMTran

@RobertOdera
Hi Robert, thank you for the response! This has been a fun problem I've been trying to mess with the past couple of days.

The same requirements as before, but, each table could have >100 columns, the example I provided above is just some dummy data.

I still want to be able to joins on date times, where the datetimes don't exactly match. I know that will require some time manipulation in order to get the closest date, and that is fine. If the table 2 date time encompasses multiple dates, I would like to take the average of those values and then join the data. If only 1 datetime in table 1 matches 1 datetime in table 2, we can do just a simple join.

We also don't need the last column explain what data is merged (that was just to explain the idea), sorry if it added some complications.

Please let me know what I can do, it will be of great help. If you need more clarification, please let me know also

RobertOdera

Sure thing, @IMTran

I will take a look, and I should have a response for you by the end of the day tomorrow (02/15). Cheers!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703