Comparing Dates within Grouping
- 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 SME,
I'm trying to compare dates within grouping with the earliest date designated as "1" and the latest as "2." My grouping is by Page Number, each number has two records. Attached is a sample dataset. Column D is what I would like to get based on the date comparison with the page grouping.
Appreciate your help.
Thanks,
kwl
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@knnwndlm
I would just first give a recordID to be used later to restore the original data order.
A sort tool will sort data by date in accending order with grouping by Page Number.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
To compare dates within your page groupings and designate the earliest date as "1" and the latest as "2," follow these steps:
Sort Data: Ensure your data is sorted by Page Number and Date in ascending order.
Use a Multi-Row Formula Tool: Drag this tool into your workflow and configure it as follows:
Group By: Set it to Page Number.
Expression:
sql
Copy code
IIF([Date] = MIN([Date]), "1", "2")
This expression checks if the date is the earliest within the group (i.e., the minimum date) and assigns "1" if true, otherwise "2".
Run the Workflow: This will create a new column indicating "1" or "2" based on the date comparison within each page grouping.
This should give you the results you need based on your dataset.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Qiu - Could you please help me understand why the formula works? Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@leefarrell - I kept getting the error message "Not enough parameters for the function "MIN." I couldn't get the IF THEN statement to work either with the same error message. I tried MINDATE and nothing came up. Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Qiu - Please ignore the posted question - I figured it out. Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
What @leefarrell shared is not possible with the Multi Row Formula Tool. The Min() function requires at least 2 input parameters, and returns the minimum of those input values, not the minimum value of a column.
@Qiu 's solution is the best solution I am aware of!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@CoG - Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@CoG
So happy to receive your positive comment. 😁
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Qiu - Thank you!
