Not in date format

[#1]

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.

[#2]

hi ,

U can try this solution :

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

[SunilChauhan]

i think this one will the best one.

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

good luck

[#4]

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?

[#5]

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........

[SunilChauhan]

in simplest way min date is oldest date

and

Max date is higest date

[#7]

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.