Date Comparison
- 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
Hello!
I have a scenario. I have 10 unique records (just as an example), each with a unique date (data-1). Also, I have a small data set with random dates (data-2). What I want to do is this. Take the date for each unique record (data-1), and compare it to the date data (data-2), and get back the date (from data-2) which is just smaller than the data-1 date but also closest. For instance.
data-1 | data-2 | ||||
Unique | Date | Date from data-2 (results) | 1/28/2020 | ||
1 | 1/30/2020 | 1/28/2020 | 2/4/2020 | ||
2 | 2/12/2020 | 2/11/2020 | 2/11/2020 | ||
3 | 1/6/2020 | 1/1/2020 | 2/18/2020 | ||
1/7/2020 | |||||
1/1/2020 |
Let me know if you have any questions.
Regards
Yaser
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @yaser ,
Does this work for you? Example attached.
Take a look and let me know 🙂
Best,
Fernando Vizcaino
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @yaser,
I think this solves your issue?
If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.
Regards,
Jonathan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Alot!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Jonathan-Sherman The solution works perfectly!. can you share the thinking process. like, I can see how you might have solved it, but I am interested in the way you reacted to the problem. maybe it will help me understand why I was stuck what I should do differently when tackling such problems in the future.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
