Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Last 3 months' data in load script

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)

1 Solution

Accepted Solutions
spividori
Specialist
Specialist

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.

View solution in original post

5 Replies
spividori
Specialist
Specialist

Hi.

where

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

Regards.



Not applicable
Author

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"

hector
Specialist
Specialist

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

spividori
Specialist
Specialist

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.

Not applicable
Author

that works!! Thanks a lot!!!