Mapping of date to next/closest date

[andymacb54]

Hi all,

It would be great to hear from anyone who can advise how to overcome a challenge I'm facing when connecting data based on specific dates to another dataset which does not necessarily have the same precise dates (hence need to find closest match).

I'm trying to identify the Price relating to a list of multiple Codes on specific Dates. I need to locate the Date and Code combination from the first data source within the secondary dataset which contains the desired Prices at various dates.   As mentioned however, the exact date from my query may not exist in the secondary (Price Data) source therefore I have to find the Price at the closest point in time for that Date and Code combination.

2 Data sources (per Text Input tools in attached):

(1) List of codes and dates for which I need to identify the relevant price value

(2) Price Data Source - list of prices at various dates

Additional comments:

Ideally the 'closest point in time' should by identified as either before or after the Request Date (whichever is really closer) - but if easier I can work with the logic being to choose e.g. just the 'previous' date which exists in the Price Data source.

Many thanks for any advice provided. And feel free to direct me to other similar topic threads.

[andymacb54]

overview of the 2 datasets, for easier review of my case.

[SPetrie · Accepted solution]

There are a few ways I can think that could accomplish this. Easiest that comes to mind is joining the two sets on the Code and doing a datatimediff on the two dates and taking the absolute value. Sort the output ascending by the difference and date and select the first result for each record and that should give you what you want. 

[andymacb54]

Thank you very much for the quick feedback.  Your recommendation achieves exactly what i needed!

I had tried to follow the DateTimeDiff approach earlier but didnt quite manage.  Your inclusion of the record ID and then selecting First N records is a very clean and effective way to identify the specific value i needed and eliminate the rest (something i was concerned about given that my real dataset has a very high volume of records).

Thanks once again.

Have a great day!