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.

dennisnet
Valued Contributor III

Date format Question

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.

1 Solution

Accepted Solutions

Re: Date format Question

Hi

See the attached file.

Hope that helps

9 Replies
christophebrault
Valued Contributor

Re: Date format Question

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,

dennisnet
Valued Contributor III

Re: Date format Question

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
Contributor

Re: Date format Question

Hi,

You can try by setting a variable

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

dennisnet
Valued Contributor III

Re: Date format Question

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.

MVP & Luminary
MVP & Luminary

Re: Date format Question

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


talk is cheap, supply exceeds demand
dennisnet
Valued Contributor III

Re: Date format Question

Thansk G.

But:

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

See attched file.

Re: Date format Question

Hi

See the attached file.

Hope that helps

Not applicable

Re: Date format Question

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

dennisnet
Valued Contributor III

Re: Date format Question

Thank you all, I used Mayil's solution.

Community Browser