This should be relatively straight forward, you just need to contend with getting the dates into the right format.
1. First step will be using a Join Tool to combine your two data sets. You should join on your Unique ID column. Please note that this will effectively duplicate your dataset, but this is intentional as we need to find the difference between every possible set of dates. We will parse the list back down to a singular DateA in the end.
2. Convert your dates into the proper format using two separate DateTime Tools. The format to convert will be "String to Date/Time format". The field to convert will be Date A / Date B and the new column name can be Date1 / Date2 respectively. The format that matches the incoming string field will be "dd-MM-yyyy".
3. Calculate DateTimeDiff using a Formula Tool. Your expression should be ABS(DateTimeDiff([Date1],[Date2],"Days"))
4. Use a Sort Tool and sort by UniqID, then by Date A, then by DateTimeDiff. All Ascending.
5. Use a Sample Tool. Select the First N rows with N = 1. Group by UniqID and DateA. This will give you one of each instance of UniqID + Date A, and since we sorted by the difference in days, it will give you the closest of each and the associated price.
6. Use a Select tool to deselect columns you no longer and need and rename any others.
Please note that this process finds the closest date regardless of whether it is before or after the original date.
Hope this helps!
