Finding Minimim Date within a record

[#1]

I have several thousand records and there are three actions different our users can take and mark the date actioned in the relevant field in our core systems, and at least one of them needs to be done by a required date.

I need to build a report that evaluates the three fields to establish if there are one or more dates then at least one or more of them are before the required date.

I want to know that if Date A or Date B or Date C has a value in it, what is the minimum date of the entered ones.

I have tried using the Min function but it looks at the database when I want to look in each record (and create a new field holding this minimum date or blank if no dates are entered).

I have tried nested IF's with If Date A < Date B (using the output to create a new field) etc but it is unweildy and the blanks appear to confuse the calculation.

In excel I simply use =min(DateA,DateB,DateC) in a new column.

[kaushiknsolanki]

Hi,

   Do you mean you need a minimum date of three date fields?

   If yes, you can try something like this.

   Data:

   Load * from data.qvd;

   Min_Temp:

    Load min(DateField1) as Min resident Data;

    Load min(DateField2) as Min resident Data;

   Load min(DateField3) as Min resident Data;

   Min:

    Load min(Min) as MinimumDate resident Min_Temp;

   Drop table Min_Temp;

   Now you will have a minimum date from all three date fields.

Regards,

Kaushik Solanki

[jagan]

Hi,

Try RangeMin(), to get minimum of the values.

Hope this helps you.

Regards,

Jagan.

[#4]

Not quite, I want the minimum date from three date fields for each record in the data base.

So record number 1 might have 3 dates, I need the Minimum,

record 2 might have 1 date, I need the minimum (which will be that date)

record 3 might have no dates so I will get a blank

record 4 might have 2 dates, I need the minimum

etc

I can then evaluate for each record whether they managed to action it in time bearing in mind they have three different options and may take all three options, one or not done anything....

[jagan · Accepted solution]

Hi,

Try RangeMin(Field1, Field2, Field3, .......), to get minimum of the values.

Hope this helps you.

Regards,

Jagan.

[kaushiknsolanki]

Hi,

   Ok so you can try this way.

   Load

   if(date1 < date2, if(date1< date3, date1, date3),if(date2 < date3,Date2,date3)) as min date

   from xyx

Regards,

Kaushik Solanki

[#7]

Thank you very much for the responses. I have used the MinRange solution and it works but other items also help with my development and learning in this product.

Thank you very much indeed.