Hi @yaser,
So if you want to find the closest date (less than) from one table to a list of dates we need to start off by having a table with all possible variations of dates between Table A and Table B. And then find the difference between each of the possible date combinations in days, sort and just pick the first per unique id (column).
This is why I start off with an append fields tool which will duplicate our list of unique dates from Table A, one record for each of dates to compare against from Table B.
Next I take the date difference between the date from Table A and Table B. Since the dates are not in an Alteryx recognised format i needed to parse the dates which I did inside one expression, however this could have been broken out. This will result in a value giving me the difference in days between the two dates. The way I created the formula means a value > 0 is a date from Table B (comparison date) that is less than the date from Table B. And vice versa. Since you wanted where the date is only less than i filtered out where the values were negative.
Finally i sort the data set in order of the date difference to give me the closest date first against each unique date, and using the sample tool to only take the first record (N=1) per unique value (i've ticked unique column in group by section of the configuration).
Does this help to answer your question?
Regards,
Jonathan
