topic Date Problem in QlikView

Date Problem

Fri, 16 Nov 2012 12:54:44 GMT

Hi,

I have a date column which is from excell file where date is not in proper order like

DateCol:

APR

MAY

1997

36319

36901

18/11/2000

36893

14/10/2002

38821

39850

20/09/2009

40486

40278

2003

2001

2004

2005

2006

2009

2010

How to create it in a proper order.

Please help for this it is urgent

Thanks

Ritul

Re: Date Problem

swuehl — Fri, 16 Nov 2012 13:21:34 GMT

You should be able to create a solution using alt() function with some possible date interpretation functions.

You'll need to decide how to handle year numbers and months names, what date do you want to assign to these?

A solution could look like this:

Set DateFormat = 'DD/MM/YYYY';

SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

LOAD *, alt(if(IsText(DateCol),Date(Date#(DateCol,'MMM')), if(DateCol < 2200,MakeDate(DateCol),Date(DateCol)))) as NewDate INLINE [

DateCol

APR

MAY

1997

36319

36901

18/11/2000

36893

14/10/2002

38821

39850

20/09/2009

40486

40278

2003

2001

2004

2005

2006

2009

2010

];

Re: Date Problem

Fri, 16 Nov 2012 13:38:22 GMT

Thanks, I have one question also what is the meaning of if(DateCol < 2200,MakeDate(DateCol),Date(DateCol) )

please explain it.

Thanks,

Ritul

Re: Date Problem

swuehl — Fri, 16 Nov 2012 13:42:34 GMT

Just noticed, that you don't really need the alt() function here, reason being that you need to check the numeric value in a condition anyway:

if(IsText(DateCol),Date(Date#(DateCol,'MMM')), if(DateCol < 2200,MakeDate(DateCol),Date(DateCol))) as DateColNew

Thanks, I have one question also what is the meaning of if(DateCol < 2200,MakeDate(DateCol),Date(DateCol) )
please explain it.

If your numeric value is smaller than 2200, I assume it's describing a year (first argument to makedate() function), if it's larger than 2200, it's a date in numeric representation, so we just need to format the text representation.