topic Lag function? in QlikView

Lag function?

Thu, 10 Feb 2011 10:50:24 GMT

Hi All,

New to QV and this is the first post, think its a basic one. I am already loving the tool and its features. I have a query as to when I am loading the data from a Oracle DB and am writing a function like below to load the data for the current year - 2 years. I have a below query,

SQL SELECT *
FROM "OPS$WSFONV"."W_ONVDWH_DAILY_SALES_LINES"
WHERE (TIME_PERIOD_ID >= 201012201.00
AND TIME_PERIOD_ID <=201112235.00);
OR (TIME_PERIOD_ID >= 201112201.00
AND TIME_PERIOD_ID <=201112235.00);

where in the above statement I am looking at date ranges from 2010 - 12 to 2011 - 12, works fine as expected but my question is if I am creating a QVD and using this to load the data, then how can I do something like a CURRENT YEAR - 2, hope you understand what am I trying to do, as this is hard coding however everytime we cannot mention the dates here and what happens after 2011, it should be 2011 - 12 to 2012 - 12... can someone help me write a function which can do this automated...?

Thanks in Advance,

ANDY

Lag function?

deepakk — Thu, 10 Feb 2011 10:57:10 GMT

hi,

I hope this is what you all are looking at

Load * from

Qvd where Year(Datefield) <= (Year(today()) -2)

Lag function?

Thu, 10 Feb 2011 10:59:41 GMT

Hello Deepak,

Thank you for the quick reply, but I think this is a QV statement, however I was thinking if this works when I do a SQL Select from DB....? I understood that once a QVD is built we can restrict data using this statement, but I want to do it at the DB level while doing a initial load??

Please help.

ANDY

Lag function?

deepakk — Thu, 10 Feb 2011 11:05:02 GMT

hi,

I dont remeber the exact sytax of oracle but either u can use TO_DATE function of TO_CHAR

eg:

Sql select * from table where TO_CHAR(Date,'YYYY') <= (TOCHAR(today() or now() ,'YYYY") -2)

Sorry I dont have Oracle system to test it.

Lag function?

Thu, 10 Feb 2011 13:26:30 GMT

Hello Deepak,

Appreciate you are trying to help me, however the attached is the error message I get when I use it and tried to do some work around but not use... can you help me any further please?

ANDY

Lag function?

deepakk — Thu, 10 Feb 2011 13:30:17 GMT

hi,

I meant today() or now() because I ddint new the function for current date.

I discussed this with my colegues and they Sys_Date() will give you the system date in Oracle.

Hence replace today() or now() with Sys_Date().

Lag function?

Thu, 10 Feb 2011 15:28:55 GMT

Hello Deepak,

Thanks again for your help, but this time I have an

Invalid Identifier error

when I try to run it.

ANDY