topic Re: Re: Using QVD in QlikView

Using QVD

Nagaraju_KCS — Wed, 21 Aug 2013 17:16:43 GMT

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..........

Re: Using QVD

Anonymous — Wed, 21 Aug 2013 17:40:13 GMT

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

have you tried with this-

Year(Today())

Year(Today())-2

Re: Using QVD

mphekin12 — Wed, 21 Aug 2013 17:49:43 GMT

You can try something like this in your script:

SELECT *

FROM TableName

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

Re: Using QVD

Nagaraju_KCS — Thu, 22 Aug 2013 05:25:23 GMT

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

Re: Using QVD

tresB — Thu, 22 Aug 2013 05:34:04 GMT

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.

Re: Re: Using QVD

rwunderlich — Thu, 22 Aug 2013 05:34:49 GMT

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

Re: Using QVD

jagan — Thu, 22 Aug 2013 05:57:04 GMT

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.