community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Joining on closest date

IMTran
6 - Meteoroid
‎02-07-2023 02:00 PM

Hi y'all, I wanted to see if there was a way to join on the closest dates depending on a time range. I have two tables that I'm trying to do a left join on. 

 

For example, if on the left table date it says: 10/18/2022 17:22, and I designate the time range to be plus or minus two hours, I would like to do a join on dates like 10/18/2022 18:34, 10/18/2022 16:78, but not 10/18/2022 15:00 or 10/18/2022 20:22 since that is outside the two hour window. 

 

The left table has this data:

DATETIME_DValue1
10/18/2022 17:220.01
10/18/2022 20:410.0209
10/19/2022 0:360.0082
10/19/2022 2:320.0436
10/19/2022 4:370.0128
10/19/2022 6:330.0102
10/19/2022 7:000.0102
10/19/2022 10:000.0066
10/19/2022 10:040.0087
10/19/2022 22:000.0195
10/20/2022 2:000.0214
10/20/2022 6:270.0103
10/20/2022 10:000.0032
10/20/2022 14:000.0071
10/20/2022 21:070.0098
10/21/2022 10:000.0087
10/21/2022 14:000.004
10/21/2022 19:000.0052
10/21/2022 22:000.0034
10/22/2022 2:000.0031
10/22/2022 6:510.0032
10/22/2022 14:000.0036
10/22/2022 19:000.0039
10/22/2022 22:000.0052
10/23/2022 2:000.0081
10/23/2022 7:150.0036
10/23/2022 10:000.0042
10/23/2022 14:000.0045
10/23/2022 19:000.0078
10/23/2022 22:000.0096
10/24/2022 2:000.0088
10/24/2022 7:090.0054
10/24/2022 10:000.0036
10/24/2022 14:000.005
10/24/2022 19:000.0066
10/24/2022 22:000.0167
10/25/2022 2:000.0138
10/25/2022 7:000.0233
10/25/2022 12:390.0144
10/25/2022 18:060.0129
10/25/2022 21:080.0192
10/25/2022 22:000.007
10/26/2022 2:000.0086
10/26/2022 7:000.016
10/26/2022 10:000.0219
10/26/2022 13:050.0149
10/26/2022 14:220.0132
10/26/2022 17:400.0071
10/26/2022 21:020.0072
10/26/2022 22:000.0053
10/27/2022 0:550.0081
10/27/2022 2:000.0079
10/27/2022 5:230.0085
10/27/2022 7:000.0078
10/27/2022 11:020.0136
10/27/2022 14:060.0175
10/27/2022 21:210.0048
10/27/2022 22:000.0044
10/28/2022 2:000.0056
10/28/2022 7:000.0223
10/28/2022 13:180.0242
10/28/2022 16:110.0149
10/28/2022 19:160.0072
10/28/2022 21:080.0039
10/28/2022 22:000.007
10/29/2022 2:000.0051
10/29/2022 5:320.0047
10/29/2022 7:000.0039
10/29/2022 10:000.0053
10/29/2022 14:000.0037
10/29/2022 19:170.0039
10/30/2022 0:160.0036
10/30/2022 3:200.0033
10/30/2022 7:120.005
10/30/2022 10:000.0061
10/30/2022 19:000.0047
10/31/2022 0:120.0059
10/31/2022 2:000.0044
10/31/2022 7:000.0111
10/31/2022 10:000.0079
10/31/2022 14:000.0044
10/31/2022 19:000.0077
11/1/2022 2:000.0071
11/1/2022 7:000.0087
11/1/2022 10:000.0066
11/1/2022 19:000.0069
11/1/2022 22:000.0039
11/2/2022 2:000.0036
11/2/2022 7:000.003
11/2/2022 10:000.0033
11/2/2022 14:000.0036
11/2/2022 19:000.0065

 

The right table has this data:

 

DATETIME_DValue2
10/18/20221.395404
10/18/2022 1:001.341625
10/18/2022 2:001.344473
10/18/2022 3:001.346781
10/18/2022 4:001.365318
10/18/2022 5:001.39561
10/18/2022 6:001.372869
10/18/2022 7:001.377094
10/18/2022 8:001.37
10/18/2022 9:001.321752
10/18/2022 10:001.376819
10/18/2022 11:001.333657
10/18/2022 12:001.382049
10/18/2022 13:001.349539
10/18/2022 14:001.348969
10/18/2022 15:001.327738
10/18/2022 16:001.363173
10/18/2022 17:001.334663
10/18/2022 18:001.332605
10/18/2022 19:001.353705
10/18/2022 20:001.361134
10/18/2022 21:001.321409
10/18/2022 22:001.311182
10/18/2022 23:001.361635
10/19/20221.366122
10/19/2022 1:001.373013
10/19/2022 2:001.376642
10/19/2022 3:001.368318
10/19/2022 4:001.332407
10/19/2022 7:001.352109
10/19/2022 8:001.34821
10/19/2022 9:001.338577
10/19/2022 10:001.346689
10/19/2022 11:001.346549
10/19/2022 12:001.342578
10/19/2022 13:001.338841
10/19/2022 14:001.322274
10/19/2022 19:001.339541
10/19/2022 20:001.381238
10/19/2022 21:001.35
10/19/2022 22:001.410129
10/19/2022 23:001.414533
10/20/20221.395495
10/20/2022 1:001.399686
10/20/2022 2:001.364944
10/20/2022 3:001.392877
10/20/2022 4:001.395438
10/20/2022 5:001.368605
10/20/2022 6:001.353466
10/20/2022 7:001.383887
10/20/2022 8:001.339471
10/20/2022 9:001.355384
10/20/2022 10:001.363854
10/20/2022 11:001.380729
10/20/2022 12:001.35
10/20/2022 13:001.400777
10/20/2022 14:001.383933
10/20/2022 20:001.348725
10/21/2022 11:001.346787
10/21/2022 12:001.364542
10/21/2022 13:001.320274
10/21/2022 14:001.3683
10/21/2022 15:001.366844
10/21/2022 16:001.357123
10/21/2022 17:001.386553
10/21/2022 18:001.367088
10/21/2022 19:001.375917
10/21/2022 20:001.350211
10/21/2022 21:001.34423
10/21/2022 22:001.365189
10/21/2022 23:001.3717
10/22/20221.378832
10/22/2022 1:001.373118
10/22/2022 2:001.37471
10/22/2022 3:001.355493
10/22/2022 4:001.376063
10/22/2022 5:001.341278
10/22/2022 6:001.375479
10/22/2022 9:001.351914
10/22/2022 10:001.376477
10/22/2022 11:001.35999
10/22/2022 15:001.373631
10/22/2022 16:001.373043
10/22/2022 17:001.37054
10/22/2022 18:001.37
10/22/2022 19:001.382522
10/22/2022 20:001.365001
10/22/2022 21:001.388127
10/22/2022 22:001.397196
10/22/2022 23:001.372355
10/23/2022 2:001.360641
10/23/2022 3:001.403056
10/23/2022 4:001.376794
10/23/2022 5:001.364964
10/23/2022 6:001.354271
10/23/2022 9:001.369667
10/23/2022 10:001.35345
10/23/2022 11:001.36569
10/23/2022 12:001.358531
10/23/2022 16:001.359126
10/23/2022 17:001.355856
10/23/2022 18:001.369244
10/23/2022 19:001.373642
10/24/2022 1:001.383989
10/24/2022 2:001.35881
10/24/2022 3:001.384344
10/24/2022 4:001.395371
10/24/2022 5:001.34589
10/24/2022 6:001.319017
10/24/2022 7:001.358339
10/24/2022 8:001.350065
10/24/2022 9:001.327486
10/24/2022 10:001.31
10/24/2022 11:001.34377
10/24/2022 12:001.341067
10/24/2022 13:001.315519
10/24/2022 14:001.380168
10/24/2022 15:001.396665
10/24/2022 16:001.398192
10/24/2022 17:001.37
10/24/2022 18:001.385058
10/24/2022 19:001.366556
10/24/2022 22:001.38804
10/24/2022 23:001.33261
10/25/20221.364816
10/25/2022 1:001.376929
10/25/2022 2:001.350296
10/25/2022 3:001.357138
10/25/2022 4:001.304488
10/25/2022 5:001.297992
10/25/2022 6:001.342688
10/25/2022 7:001.341821
10/25/2022 8:001.33
10/25/2022 9:001.33
10/25/2022 12:001.338557
10/25/2022 13:001.381665
10/25/2022 14:001.39
10/25/2022 16:001.39
10/25/2022 17:001.355378
10/25/2022 18:001.353918
10/25/2022 19:001.334651
10/25/2022 20:001.333661
10/25/2022 21:001.321815
10/25/2022 22:001.327224
10/25/2022 23:001.356137
10/26/20221.377364
10/26/2022 1:001.336762
10/26/2022 2:001.352456
10/26/2022 3:001.316025
10/26/2022 4:001.329637
10/26/2022 5:001.308338
10/26/2022 6:001.331263
10/26/2022 7:001.366988
10/26/2022 8:001.3629
10/26/2022 9:001.364238
10/26/2022 10:001.360918
10/26/2022 11:001.347886
10/26/2022 12:001.37055
10/26/2022 13:001.37479
10/26/2022 14:001.383907
10/26/2022 15:001.364449
10/26/2022 16:001.368529
10/26/2022 17:001.363876
10/26/2022 18:001.37528
10/26/2022 20:001.39
10/26/2022 21:001.374917
10/26/2022 22:001.36686
10/26/2022 23:001.360201
10/27/20221.37201
10/27/2022 1:001.35766
10/27/2022 2:001.370382
10/27/2022 3:001.365638
10/27/2022 4:001.367243
10/27/2022 5:001.39
10/27/2022 6:001.364981
10/27/2022 7:001.364852
10/27/2022 8:001.363774
10/27/2022 9:001.368798
10/27/2022 10:001.380932
10/27/2022 11:001.385653
10/27/2022 12:001.367088
10/27/2022 13:001.360153
10/27/2022 14:001.408554
10/27/2022 15:001.363381
10/27/2022 16:001.4
10/27/2022 17:001.349703
10/27/2022 18:001.352989
10/27/2022 19:001.349017
10/27/2022 20:001.395874
10/27/2022 21:001.377004
10/27/2022 22:001.382905
10/27/2022 23:001.372492
10/28/20221.383332
10/28/2022 1:001.356437
10/28/2022 2:001.374788
10/28/2022 3:001.379554
10/28/2022 4:001.373609
10/28/2022 9:001.354692
10/28/2022 13:001.352599
10/28/2022 14:001.35
10/28/2022 15:001.39879
10/28/2022 16:001.342637
10/28/2022 17:001.34565
10/28/2022 18:001.380782
10/28/2022 19:001.383649
10/28/2022 20:001.382828
10/28/2022 21:001.350602
10/28/2022 22:001.379991
10/28/2022 23:001.404889
10/29/20221.39
10/29/2022 1:001.355038
10/29/2022 2:001.398991
10/29/2022 3:001.381034
10/29/2022 4:001.3483
10/29/2022 5:001.371839
10/29/2022 6:001.316535
10/29/2022 7:001.32293
10/29/2022 8:001.384449
10/29/2022 9:001.385804
10/29/2022 10:001.378237
10/29/2022 11:001.373455
10/29/2022 12:001.348323
10/29/2022 13:001.339352
10/29/2022 14:001.365962
10/29/2022 16:001.380404
10/29/2022 17:001.335299
10/29/2022 18:001.369844
10/29/2022 19:001.361836
10/29/2022 20:001.348104
10/29/2022 21:001.341607
10/29/2022 22:001.336928
10/29/2022 23:001.362411
10/30/20221.365443
10/30/2022 1:001.339254
10/30/2022 2:001.328197
10/30/2022 3:001.300668
10/30/2022 4:001.376739
10/30/2022 7:001.365497
10/30/2022 8:001.36
10/30/2022 9:001.351594
10/30/2022 10:001.383343
10/30/2022 11:001.350621
10/30/2022 12:001.367404
10/30/2022 13:001.33865
10/30/2022 14:001.366388
10/30/2022 15:001.348838
10/30/2022 16:001.365563
10/30/2022 17:001.331794
10/30/2022 18:001.325087
10/30/2022 19:001.358729
10/30/2022 20:001.348796
10/30/2022 21:001.353439
10/30/2022 22:001.334892
10/30/2022 23:001.325557
10/31/20221.312197
10/31/2022 1:001.322009
10/31/2022 2:001.316753
10/31/2022 3:001.323835
10/31/2022 4:001.348922
10/31/2022 5:001.36551
10/31/2022 6:001.306738
10/31/2022 7:001.332914
10/31/2022 8:001.315796
10/31/2022 9:001.358511
10/31/2022 10:001.344554
10/31/2022 11:001.3
10/31/2022 12:001.355132
10/31/2022 13:001.353319
10/31/2022 14:001.305075
10/31/2022 15:001.323809
10/31/2022 18:001.377634
10/31/2022 19:001.36873
10/31/2022 20:001.387145
10/31/2022 21:001.37164
11/1/2022 1:001.345585
11/1/2022 2:001.353237
11/1/2022 3:001.383596
11/1/2022 4:001.350733
11/1/2022 5:001.337422
11/1/2022 6:001.334222
11/1/2022 7:001.367011
11/1/2022 8:001.35895
11/1/2022 9:001.368209
11/1/2022 10:001.359287
11/1/2022 11:001.348467
11/1/2022 12:001.368885
11/1/2022 13:001.385087
11/1/2022 18:001.373843
11/1/2022 19:001.383234
11/1/2022 20:001.345533
11/1/2022 21:001.352218
11/1/2022 22:001.317682
11/1/2022 23:001.353458
11/2/20221.357054
11/2/2022 2:001.346436
11/2/2022 3:001.332302
11/2/2022 4:001.365558
11/2/2022 5:001.337686
11/2/2022 6:001.324106
11/2/2022 9:001.314799
11/2/2022 10:001.249617
11/2/2022 11:001.252009
11/2/2022 12:001.352135
11/2/2022 13:001.326178
11/2/2022 14:001.352064
11/2/2022 15:001.355281
11/2/2022 16:001.321422
11/2/2022 17:001.338068
11/2/2022 18:001.3636
11/2/2022 19:001.365102
Labels:
Reply
0 Likes

Accepted SolutionSolved! Go to Solution.

3 REPLIES 3
ShankerV
17 - Castor
‎02-07-2023 02:25 PM

Hi @IMTran 

 

One way of doing this.

 

ShankerV_0-1675808681145.png

 

 

Joining on closest date.yxmd
Reply
0 Likes
Verakso
11 - Bolide
‎02-07-2023 02:30 PM

I have no idea if this comes close to what you are looking for, but I gave it a try.

The thing is, you really can't do a left or right outer join, well, that was until @NeilR made The Advanced Join Macro 

I have used that to join on your two sets of data, where I first made a Lowe and an Upper datetime for the +- 2 hours inteval

 

Verakso_0-1675808973352.png

You can play with the attached workflow

/Verakso (ツ)

 

JoinOnClosestDate.yxzp
Reply
DanM
Alteryx Community Team
Alteryx Community Team
‎02-07-2023 02:30 PM

Here's a suggestion;

 

Since your sample data has the same format, add a Formula tool after each Input tool and create a new field. Name the field it whatever you want, but maybe "Input". In one Formula tool add in the expression "Input 1" and leave as a string and the other Formula tool do the same but name it "Input 2".

 

Then use the Union tool to put the data together. After the Union tool use a Sort tool on your DATETIME_D field.

 

You can then use a Filter tool to do a less than this date and more than this date expression. Make sure your date field is listed as Date or DateTime in the meta data.

 

That will give you the data for bother data sets within the time range you want. You will also be able to see what data came from what data set with the new field you created.

 

Hope that helps.

Reply
learn more about the python upgrade coming in may 2024
Labels