Finding the min and max of a date range

Hi Padma,

This is a great question and you don't really need a script or custom function to perform this.  It can be done using IDEA's built in funcationality that then can be automated if you want to create a script.

So using your example I brought the info into IDEA.

The next step is I want to sort the file in order by Vendor and then by Date.  I did this by creating a new file using the Sort function.

This gave me a new file where the Vendor and Date are in order.

I then did two summaries, each one slightly different.  The first summary I want to get the Vendor and the Earliest date.  I summarized by Vendor and in the option where I can select additional fields I selected the Date field.  Also at the bottom I selected "Use fields from first occurance".  This will give me a file with a unique Vendor per line, in this case the Earliest date.

I would then rename the Date field to Earliest Date.

Now I would perform the exact same summary but in this case I would select "Use fields from last occurrence" and rename the date field to Latest Date.

The final step is joining the two summaries together. You can take either file as the primary and secondary, as they are both the same it doesn't matter.  For fields in the primary I selected the Vendor and the Date field and in the secondary I only selected the Date field (or whatever they have been renamed to).  The match would be on Vendor and the matching order would be Matches only (but All records in primary file would also work). 

You now have your file by vendor with the earliest and latest date.  As these are all IDEA steps they can be easily created into a Script.

This was of great help.  Hats off for the speed at which you respond.
Good to be part of this forum.
 
Regards
Padma 

Another method to obtain the same results would be extraction using the Top Records option. 

Using Brian's example, you would select the number of records to extract as "1". Select the type as "Top records" which will give you the last check date. In the Top records for area, select the DATE field and then group by VENDOR. Perform the same task again but this time selecting the type as "Bottom records" which will give you the first date.

Using the 2 new files, join them by VENDOR.

This method is neither more efficient nor simpler but merely offers a different way to accomplish the same good results.

Hi Brian,
Even i was facing a simillar issue, i tried to do it your way, by doing a summarisation, but for some reason, in the summarisation dialogue box, these two options are inactive/not highlighted:
 --> Use fields from first occurence.
--> Use fields from the last occurence.
 
I am unable to chose between the last occurence or first occurence, and this common for all my collegues using IDEA as well, can you please guide me and tell me if there is any way around this in the summarization feature.

Thanks Brian, that really helped me out !