Hide years data.

[#1]

     Hello Experts,

I have data from database 2010 -2015. but I want show only last 3 years data. I dont want 2010, 2011 data into my Qlikview dashbords. How to block it and how to take only those two years data backup into SaveQVD_2010.QVD, SaveQVD_2011.QVD.?/

Thanks

[Anonymous]

Hi San,

Please use below code for your requirement:

TempTable:
LOAD Distinct Year(Date) AS Year
FROM QVD\Main.qvd(qvd)
WHERE Date < AddYears(Today(), -3)
Order by Year(Date) asc;

NoConcatenate

Table:
Load Year,Min(Year) as minYear, max(Year) as maxYear
Resident TempTable;

Let vMin=Peek('minYear', 0, 'Table');
Let vMax=Peek('maxYear', 0, 'Table');

FOR i=$(vMin) to $(vMax)
LET vYear = Peek('Year',$(i),'Table');

YearWise:
NoConcatenate
LOAD * FROM QVD\Main.qvd(qvd)
Where Year(Date) = '$(vYear)';

STORE YearWise into TableName_$(vYear).qvd;
DROP Table YearWise;

Next

DROP Table TempTable;
DROP Table Table;

Regards

Neetha

[oknotsen]

That is why my code example makes the years he does not want not end up in his final data model.

[Anonymous]

Hi San,

or try:

Please use below code for your requirement:

//Please use below code for your requirement for generating QVD's(2010 and 2011):

TempTable:
LOAD Distinct Year(Date) AS Year
FROM QVD\Main.qvd(qvd)
WHERE Date < AddYears(Today(), -3)
Order by Year(Date) asc;

NoConcatenate

Table:
Load Year,Min(Year) as minYear, max(Year) as maxYear
Resident TempTable;

Let vMin=Peek('minYear', 0, 'Table');
Let vMax=Peek('maxYear', 0, 'Table');

FOR i=$(vMin) to $(vMax)
LET vYear = Peek('Year',$(i),'Table');

YearWise:
NoConcatenate
LOAD * FROM QVD\Main.qvd(qvd)
Where Year(Date) = '$(vYear)';

STORE YearWise into TableName_$(vYear).qvd;
DROP Table YearWise;

Next

DROP Table TempTable;
DROP Table Table;

//Use below script for visualisation:
Table:
LOAD *
FROM QVD\Main.qvd(qvd)
WHERE Date > AddYears(Today(), -3)
Order by Year(Date) asc;

Regards

Neetha