Trying to Join data with matching critera

[IMTran]

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.

[RobertOdera · Accepted solution]

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!

[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?

[RobertOdera]

Hi, @IMTran 

Thanks for marking ky answer as an acceptable solution.

Yes, IMHO, there is always an Alteryx solution.

Please provide a before and after for your new requirement, and I will explain how to adapt or extend the above solution so you can do it.

Good to know that value 2 is optional, as it takes quite some effort to display the logic rationale.

Cheers!

[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!

[IMTran]

Thank you so much for your help!

[RobertOdera]

Here you go @IMTran 

-standardized span

-first join is by direct match, second join by proxy match

-transpose configured to anticipate multiple columns dynamically

-read comments in-flow notations and in-calculations

Absent an actual washed accurate sample, this is now at diminishing returns for further conceptual treatments.

Still, I hope this helps you - please mark it as an acceptable solution if it works for you. Cheers!

[RobertOdera]

@IMTran 

You're most welcome!

Please let me/ the community know if you need anything else. Cheers!