Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Date Problem

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

3 Replies
MVP
MVP

Re: Date Problem

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

];

Not applicable

Re: Date Problem

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

please explain it.

Thanks,

Ritul

MVP
MVP

Re: Date Problem

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.

Community Browser