Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
qv_testing
Specialist II
Specialist II

Using QVD

Hi All,

I have order table (Suppose i have 2010,2011,2012 years data in my table)

suppose i am adding some new data 2013 years data...

in my QVD i want to show last three years of data.......(means 2011,2012,2013)....

If am adding some 2014 data i want show 2012,2013,2014 years of data......

I want to show last three years of data............

How to implement in QVD ...

Thanks in Advance..........

6 Replies
Anonymous
Not applicable

if you have 2014 data then what will be the current date now

have you tried with this-

Year(Today())

Year(Today())-2

mphekin12
Specialist
Specialist

You can try something like this in your script:

SELECT *

FROM TableName

WHERE DateField>= AddYears(yearstart(today()),-2)

qv_testing
Specialist II
Specialist II
Author

Hi mphekin,

Today() returns only current date. but i don't want current date year

suppose i have 2000,2001,2002, 2003,2004 i want return this time 2002,2003 and 2004..

when i am adding some new data 2005 year.. this time i want to return 2003,2004, and 2005

I want to return only last three years of data

tresesco
MVP
MVP

Something like this?

Tmp:

Load

          Max(YearField) as MaxYear

From <New Year Data> ;

Let vMaxYear = Peek('MaxYear') ;

Drop table Tmp;

LastthreeYearData:


Load * From <New Year Data>;

Load * From <QVD> Where YearField> ($(vMaxYear)-3) ;

Note: to handle QVD, you might have to tweak the above script a bit.

Hope this helps.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

If you want the latest three years, based on the contents of the QVD, you will first have to read the complete QVD to determine the max year. The you can use that max year as a filter

MaxTemp:

LOAD max(Year) as MaxYear FROM myqvd.qvd (qvd);

LET vMaxYear = peek('MaxYear');

DROP TABLE MaxTemp;

LOAD *

FROM myqvd.qvd (qvd)

WHERE Year > $(vMaxYear)-3

;

-Rob

http://masterssummit.com

http://robwunderlich.com

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

Get max year from qvd using below script and save it in variable.

MaxYear:
LOAD Max(Year) as MaxYear
FROM myqvd.qvd (qvd);

LET vMaxYear = Peek('MaxYear');

DROP TABLE MaxTemp;

Load data for the last three years in the qvd by using the variable value

Data:
LOAD *
FROM myqvd.qvd (qvd)
WHERE Year > $(vMaxYear)-3

Hope this helps you.

Regards,

Jagan.