topic Not in date format in QlikView

Not in date format

Fri, 28 May 2010 08:37:00 GMT

Hi All,

I am woking on the following set of data:

Data:

LOAD * INLINE [
ID,StartDate,EndDate
1,1/1/2009,1/12/2009
1,1/22/2009,1/31/2009
2,1/1/2009,1/31/2009
3,1/10/2009,1/27/2009
4,1/1/2009,1/12/2009
1,12/23/2008,12/30/2008
1,11/1/2008,12/1/2008
2,11/30/2008,12/31/2008
3,12/10/2008,12/27/2008
4,11/27/2008,12/15/2008
];

now i want to get the min and max date by writing :

load min(StartDate) as minDate,
max(EndDate) as maxDate
resident Data;

It is showing following result:

Mindate: 39753

Maxdate:39844

It is not in date format. While i used

SET DateFormat='M/D/YYYY';

in the starting.

Please clarify it.

Thanks in advance.

Not in date format

Fri, 28 May 2010 09:17:59 GMT

hi ,

U can try this solution :

load date(min(StartDate)) as minDate,
date( max(EndDate)) as maxDate
resident Data;

Not in date format

SunilChauhan — Fri, 28 May 2010 09:27:49 GMT

i think this one will the best one.

load min(Date(StartDate)) as minDate, max(Date(EndDate)) as maxDate resident Data;

good luck

Not in date format

Fri, 28 May 2010 12:13:42 GMT

Thanks Moshegolan & Tom. It is working.
Still I am unable to understand why Mindate: 39753,Maxdate:39844 got as a result previously.
Is it a garbage value or what?

Not in date format

Fri, 28 May 2010 12:53:21 GMT

Hi Mansi,

These values are not garbage values. These are the number representation of that particular dates. Actually if you go through the reference manual you will find that, in QlikView date starts from a particulr date which I dont remeber now, So that date is given a particular number representation and each date after that is incremented by some value. So each and every date in QlikView has its own number representation. Only if the foramat for a specific date is specified, the number representation is converted to its particular date format. Hope this helps. For more info refer reference manual.

Thanks joseph........

Not in date format

SunilChauhan — Fri, 28 May 2010 13:02:44 GMT

in simplest way min date is oldest date

and

Max date is higest date

Not in date format

Fri, 28 May 2010 13:22:14 GMT

Thanks Thopz it is really worth while info.

Thank you so much.

I have posted one more issue ie http://community.qlik.com/forums/p/29697/114180.aspx#114180.

If you have any idea regarding this pls let me know.