topic Re: Extract month from MM/DD/YYYY format in QlikView

Extract month from MM/DD/YYYY format

Thu, 05 Feb 2015 15:55:47 GMT

Hi everyone,

I am trying to extract the month from my date format, however I am not doing it correctly. Should I create a timestamp for my dates and extract the month only after?

Re: Extract month from MM/DD/YYYY format

rubenmarin — Thu, 05 Feb 2015 16:10:47 GMT

Hi Pedro, if that's not your regional date format you call tell QV wich date format is, to extract month you can use:

=Month(Date#(Day, 'MM/DD/YYYY'))

Notice that internally date is a number, looking on your Day list box you can see that values are left aligned, this tells that it is a string, not a number (numbers are right aligned), so for Qlikview Day field is text, not number (=not date).

Re: Extract month from MM/DD/YYYY format

anbu1984 — Thu, 05 Feb 2015 16:14:31 GMT

First convert your input to Date using Date#()

Load *,Month(Day) As Mth;

Load Date#(Day,'M/D/YYYY') As Day,Count From Source;

Re: Extract month from MM/DD/YYYY format

Thu, 05 Feb 2015 16:28:35 GMT

But I am already doing it!

From my script:

" Date#(('$(vFileDate)'),'MM-DD-YYYY') as Day,"

But only I do it for the secound time it works:

" Date#(Day, 'MM/DD/YYYY') as Day234"

In Qlikview Day is a string but Day234 is already a date. Why it only works when I call for the second time the same function?

Re: Extract month from MM/DD/YYYY format

rubenmarin — Thu, 05 Feb 2015 16:30:45 GMT

I think is for the separator, in the first case you're using '-' instead of '/'

Re: Extract month from MM/DD/YYYY format

Thu, 05 Feb 2015 16:38:06 GMT

The machine is always right. I dont know why I didnt notice that.

Thanks!

Re: Extract month from MM/DD/YYYY format

jpapador — Thu, 05 Feb 2015 16:41:39 GMT

Replace Month(Day) in your script with the following:

Month(Num(Date#(Day, 'M/DD/YYYY')))