min/max date

[#1]

Hi everyone,

I need to get the min and max value from a date field. The date is in YYYY-MM-DD format.

Thanks!

[prieper]

Use the MIN- or MAX-function:

LOAD MIN(MyDate) AS MinDate, MAX(MyDate) AS MaxDate FROM ....

HTH
Peter

[#3]

For some reason those functions do not work. When I reload the script an eror message saying "Invalid expression" is displayed.

[#4]

Hi

Please post your script so we can take a look.

/Fredrik

[#5]

perhaps your date is in string then try using the makedate function

or use date(min(date#(MyDate))) as MinDate

[#6]

LOAD



 Number,
 Date,
min(Date) as MinDate
FROM
C:\Users\operator_bi\Desktop\proj\invoices\globul_invoice_2008_11_01.xls
(
 biff, embedded labels, table is details_1$);

[#7]

I have similiar problem. I wrote the following code :

LOAD CustomerName, Date, Value, min(Date) as MinDate

FROM
C:\Users\operator_bi\Desktop\proj\invoices\globul_invoice_2008_11_01.xls
(biff, embedded labels, table is details_1$)

GROUP BY CustomerName

and i get "Invalid expression". I tryed every option with date formating. I read several other post, but no result. So if you know anything, I will be glad.

[#8]

Hi

When you use an aggregation function, like min, max, avg etc. you must use a group by clause and specify all other fields that aren't being aggregated.
For instance, if you want to do this:

LOAD
A,
B,
max(C) as C

you must have a group by clause like this:

group by A, B

This should work for you:
LOAD
Number,
min(Date) as MinDate
FROM C:\Users\operator_bi\Desktop\proj\invoices\globul_invoice_2008_11_01.xls (biff, embedded labels, table is details_1$)
GROUP BY Number;

Refer to the helpfile and look for Aggregation functions to get some examples.

/Fredrik

[#9]

Do this

LOAD CustomerName,

Date,

Value,

min(Date) as MinDate

FROM
C:\Users\operator_bi\Desktop\proj\invoices\globul_invoice_2008_11_01.xls
(biff, embedded labels, table is details_1$)

GROUP BY CustomerName,Date,Value;