topic min/max date in QlikView

min/max date

Thu, 21 Jan 2010 09:52:29 GMT

Hi everyone,

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

Thanks!

min/max date

prieper — Thu, 21 Jan 2010 09:55:28 GMT

Use the MIN- or MAX-function:

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

HTH
Peter

min/max date

Thu, 21 Jan 2010 10:05:05 GMT

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

min/max date

Thu, 21 Jan 2010 11:07:30 GMT

Please post your script so we can take a look.

/Fredrik

min/max date

Thu, 21 Jan 2010 12:07:20 GMT

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

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

min/max date

Thu, 21 Jan 2010 13:17:02 GMT


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$);

min/max date

Thu, 21 Jan 2010 13:51:22 GMT

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.

min/max date

Thu, 21 Jan 2010 18:33:31 GMT

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

min/max date

Fri, 22 Jan 2010 07:30:55 GMT

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;