Pickup the nearest date for the given price
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi ,
I have this scenario :
UniqID | Date A | Price A | UniqID | Date B | Price B | |
A | 24-10-2023 | 100 USD | A | 23-10-2023 | 150 USD | |
A | 21-10-2023 | 50 USD | A | 22-10-2023 | 100 USD | |
A | 25-10-2023 | 10 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 | DateA | New Price |
A | 24-10-2023 | 150 USD |
A | 21-10-2023 | 100 USD |
A | 25-10-2023 | 150 USD |
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
 
 
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
