topic Last 3 months' data in load script in QlikView

Last 3 months' data in load script

Fri, 21 Jan 2011 18:37:01 GMT

How come the data return does not recognize my "where" clause? I was the data to return only the last 3 months' data. Can anyone help me to translate this into the correct statement?

LOAD SELECT * FROM dbo. A

WHERE Month(TIME_INS) >= month(current date) -3 AND Month(TIME_INS) <= month(current date)

Last 3 months' data in load script

spividori — Fri, 21 Jan 2011 21:59:15 GMT

Hi.

where

date(TIME_INS)<=MonthEnd(today()) and date(TIME_INS)>=MonthStart(today(),-2) ;

Regards.

Re:Last 3 months' data in load script

Fri, 21 Jan 2011 22:41:24 GMT

Doesn't work.... please help!!!!

it said "SQL Error:[IBM][CLI Driver][DB2/LINUXX8664] SQL0440N No authorized routine named "TODAY"/ "MonthStart" and "MonthEnd" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

SQL Scriptline:
SQL State:42884"

Last 3 months' data in load script

hector — Fri, 21 Jan 2011 22:56:34 GMT

Hi, the answer provided by Sandro, is what you asked for, but in a "QlikView Environment", so this is valid code in a Load statement, you can't use the functions in a SQL Select... from statement (they are exceptions to this rule)

You need to use the database functions

For example to extract the month from a timestamp:

in Oracle is extract(month from field)
in Sql server is month(field)
in qlikview is month(field)

Maybe for your database works now() function, and find the functions for add days or months to your field

Rgds

Last 3 months' data in load script

spividori — Fri, 21 Jan 2011 23:12:58 GMT

Hi.

If Hector is right.
You could also try the following:

let hDate = date(today()+(day(today())-1));
let dDate = date(MonthStart(today(),-2));

where date(TIME_INS)>=date('$(dDate)') and date(TIME_INS)<=date('$(hDate)');

Regards.

Last 3 months' data in load script

Mon, 24 Jan 2011 17:26:44 GMT

that works!! Thanks a lot!!!