topic Re: Month function in Script in QlikView

Month function in Script

Thu, 01 Nov 2012 18:27:24 GMT

Hi:

Full disclosure I'm not a QV developer, just playing one on TV.

Trying to load several fields from and Oracle DB and in the process create a new Month and Year field based upon a timestamp field that is coming in.

DETL:

SQL SELECT

ID,

CREATE_DATE,

MONTH (CREATE_DATE) as CREATEMONTH;

And get:

ErrorSource: OraOLEDB, ErrorMsg: ORA-00904: "MONTH": invalid identifier

Can I do this or am I just doing somethign stupid?

Thanks in advance,

Jim

hic — Thu, 01 Nov 2012 18:34:34 GMT

In principle you should be able to do this, but it is better if you use a preceding load:

Load

ID,

CREATE_DATE,

MONTH (CREATE_DATE) as CREATEMONTH;

SQL SELECT * FROM ...

HIC

Thu, 01 Nov 2012 18:49:26 GMT

This worked perfectly. Thanks for the quick reply.

CELAMBARASAN — Thu, 01 Nov 2012 18:50:31 GMT

Hi,

You can use To_Char function too

To_Char(Date_Field,'MM') = 01

To_Char(Date_Field,'MON') = JAN

To_Char(Date_Field,'YY') = 03

To_Char(Date_Field,'YYYY') = 2003

Also you can go with preceding load as suggested by Henric Cronström.

Thu, 01 Nov 2012 18:52:52 GMT

Sorry, Gysbert, accidentally deleted your post but it did work as well. All great options.