topic Re: Date format Question in QlikView

Date format Question

Anonymous — Tue, 09 Oct 2012 08:22:16 GMT

Dear Qlikview community members,

Question:

How do I change a text monthname (3 leters) to a month number?

So how does Feb become 02?

Thanks!

Dennis.

Re: Date format Question

christophebrault — Tue, 09 Oct 2012 08:37:10 GMT

Hi,

If you have 12 month in letters, you can use a simple map table to transform to number :

first load a table like this for 12 month with the name and coresponding number :

Mapping

MAP_MONTH:

LOAD * INLINE [

Letter, Number

Jan, 1

Feb, 2

Mar, 3

Apr, 4

];

then use it when loading your data :

load ...,

ApplyMap('MAP_MONTH',Month) as No_Month,

Re: Date format Question

Anonymous — Tue, 09 Oct 2012 08:52:29 GMT

Yes that was the solution I have now, but there must be a way to let Qlikview recognize the months when using:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

In the script. If not I will use this 🙂

Re: Date format Question

manisha_uit — Tue, 09 Oct 2012 08:56:41 GMT

Hi,

You can try by setting a variable

=Num(Month('02/02/2012')) returns 2

Re: Date format Question

Anonymous — Tue, 09 Oct 2012 09:14:41 GMT

Thanks Manisha but my date field looks like '2 Feb 2012' and is a text field.

I already used :

KeepChar(datefield, 'JanFebMarAprMayJunJulAugSepOctNovDec') as Month,

So my field Month has now 'Feb' as text. But Qlikview does not recognize it as a month.

Re: Date format Question

Gysbert_Wassenaar — Tue, 09 Oct 2012 13:45:08 GMT

Try this:

=date(date#(MyDateString,'dd MMM yyyy'),'dd-MM-yyyy')

=date(date#(''12 feb 2012,'dd MMM yyyy'),'dd-MM-yyyy') returns a date formatted as 12-02-2012

Re: Date format Question

Anonymous — Wed, 10 Oct 2012 08:29:43 GMT

Thansk G.

But:

=date(date#('12 feb 2012','dd MMM yyyy'),'dd-MM-yyyy') Returns: -

See attched file.

Re: Date format Question

MayilVahanan — Wed, 10 Oct 2012 08:49:55 GMT

See the attached file.

Hope that helps

Re: Date format Question

Wed, 10 Oct 2012 08:56:03 GMT

Hi,

Maybe you could try this one.

Date(Date#('Feb','MMM'),'MM')

if the format is this '2 Feb 2012'

you could try this

Date(Date#(mid('2 Feb 2012',3,3),'MMM'),'MM')

Regards,

Janzen

Re: Date format Question

Anonymous — Wed, 10 Oct 2012 14:19:39 GMT

Thank you all, I used Mayil's solution.