topic Problems with Year() Function in QlikView

Problems with Year() Function

Mon, 03 Jan 2011 20:34:20 GMT

Hello to everybody,

I'm new with qilikview and trying to make my first project.

I import data from an firebird database via ODBC. When adding the Year function (as stated below) the script makes an error:

SQL##f - SqlState: HY000, ErrorCode: 4294967192, ErrorMsg: [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 3, column 5
Year
SQL SELECT ID,
DATUM,
Year(Datum) as Jahr,
WAEHRUNG,
"WAEHRUNG_KURS",
"MWST_SATZ",
"GESAMTSUMME_NETTO",
HEADER,
"HAUPT_ID",
"BEZAHLT_BRUTTO"
FROM "V_RECHNUNG"

I'm afraid I make a very trivial mistake but I don't know where and why? Datum is a timestamp format and without the year function the script works!

Thanks for every help!!!

Christoph

Problems with Year() Function

hector — Mon, 03 Jan 2011 20:55:46 GMT

Hi, i don't know Firebird SQL, but it depends on every DB Engine how to extract this values, for example in MSSQL is Year(Field) as Year, but in Oracle is Extract(Year From Field) as Year

If you don't know how is the function try this method

Load
*,
 Year(DATUM) as Jahr
;
 SQL SELECT ID,
 DATUM,
 WAEHRUNG,
 "WAEHRUNG_KURS",
 "MWST_SATZ",
 "GESAMTSUMME_NETTO",
 HEADER,
 "HAUPT_ID",
 "BEZAHLT_BRUTTO"
FROM "V_RECHNUNG"

So, you are using the QlikView's year() function

Rgds

Problems with Year() Function

Mon, 03 Jan 2011 20:58:48 GMT

QlikView field names ARE case-sensitive, so you must be consistent. If your field is spelled DATUM in all capital letters, your year function should be Year(DATUM).

Problems with Year() Function

rwunderlich — Mon, 03 Jan 2011 21:08:19 GMT

I don't think Year() is a function defined to Firebird. If you are trying to use the Qlikview Year() function, is should be used in a preceeding load like this:

LOAD *,
Year(DATUM) as Jahr
;
SQL SELECT ID,
DATUM,
WAEHRUNG,

"WAEHRUNG_KURS",
"MWST_SATZ",
"GESAMTSUMME_NETTO",
HEADER,
"HAUPT_ID",
"BEZAHLT_BRUTTO"
FROM "V_RECHNUNG"
;

-Rob

Problems with Year() Function

hector — Mon, 03 Jan 2011 21:08:36 GMT

Hi, as i said it's a SQL's DML problem

http://www.firebirdsql.org/refdocs/langrefupd20-extract.html

Use Extract from instead of Year() OR, put the year() function between the Load and ";" before the SQL Select Statement

Rgds

Problems with Year() Function

Tue, 04 Jan 2011 08:44:18 GMT

Thanks a lot for your support! Extract function works.