Date format Question

[Anonymous]

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.

[christophebrault]

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,

[Anonymous]

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 🙂

[manisha_uit]

Hi,

You can try by setting a variable

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

[Anonymous]

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.

[Gysbert_Wassenaar]

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

[Anonymous]

Thansk G.

But:

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

See attched file.

[MayilVahanan · Accepted solution]

Hi

See the attached file.

Hope that helps

[#9]

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

[Anonymous]

Thank you all, I used Mayil's solution.