Pickup the nearest date for the given price

Question

Hi ,

I have this scenario :

UniqID Date APrice A  UniqID Date B Price B A24-10-2023100 USD  A23-10-2023150 USD A21-10-202350 USD  A22-10-2023100 USD A25-10-202310 USD

Two data sets A and B need to be compared .

Based on the date A , solution need to choose the nearest date B and subsequent price B as shown below .

Thanks in advance.

UniqID DateANew PriceA24-10-2023150 USD A21-10-2023100 USD A25-10-2023150 USD

TheMattLeonard · Answer

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!

Prometheus · Answer

@Nandakishore Try this solution. I used the Append Fields tool to get all the records in both datasets. Then I used an absolute value of the DateTimeDiff to get the difference in days between Date A and Date B. Then I sorted the data and used the Sample tool to pull the first record grouped by Date A.

Nearest Date.yxmd