topic Re: Pulling year and month from dates? in QlikView

Pulling year and month from dates?

Thu, 06 Sep 2012 14:00:43 GMT

Are there any functions in Qlikview that will allow me to select my data depending on the month and year that it's associated with? I have a few fields with dates but I don't know how it's possible to extract some of that information to create another field (i.e. have a field for months: Jan, Feb, mar and a field for year: 2005, 2006, 2007, etc)

Thank you!

Re: Pulling year and month from dates?

Miguel_Angel_Baeyens — Thu, 06 Sep 2012 14:31:03 GMT

Hi,

Sure, there are quite a bunch! Take a look at the following sample code, based on the "DateField" field that stores values like "06/09/2012" in the form "DD/MM/YYYY":

Calendar:
LOAD DateField AS CompleteDate,
     Month(DateField) AS MonthField,
     Year(DateField) AS YearField,
     Day(DateField) AS DayField
FROM File.qvd (qvd);

Hope that helps.

Miguel

Re: Pulling year and month from dates?

Thu, 06 Sep 2012 14:44:06 GMT

Hi Miguel,

(Forgive me but I just started using Qlikview)

So I pasted what you wrote into Edid Script and replaced DataField with the name of one of the date fields within a table I'm working on. So I get something that looks like this:

Calendar:

LOAD PlacedDate AS CompleteDate,

Month(PlacedDate) AS MonthField,

Year(PlacedDate) AS YearField,

Day(PlacedDate) AS DayField

FROM File.qvd (qvd);

It fails when I hit Reload so I'm assuming that this script alone won't create the qvd file? If so, how would I create one?

Re: Pulling year and month from dates?

Fri, 07 Sep 2012 09:34:52 GMT

Hi,

Directly place the below columns into your existing table, then it works fine.

    PlacedDate,
     Month(PlacedDate) AS MonthField,
     Year(PlacedDate) AS YearField,
     Day(PlacedDate) AS DayField