Comparing Dates within Grouping

[knnwndlm]

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

[Qiu · Accepted solution]

@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.

[leefarrell]

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.

[knnwndlm]

@Qiu - Could you please help me understand why the formula works?  Thank you!

[knnwndlm]

@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!

[knnwndlm]

@Qiu - Please ignore the posted question - I figured it out.  Thank you!

[CoG]

@knnwndlm 

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!

[knnwndlm]

@CoG - Thank you!

[Qiu]

@CoG 
So happy to receive your positive comment. 😁

[knnwndlm]

@Qiu - Thank you!