topic Re: Hide years data. in QlikView

Hide years data.

Tue, 24 Feb 2015 22:37:28 GMT

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

Re: Hide years data.

oknotsen — Tue, 24 Feb 2015 22:45:40 GMT

TempTable:

Load *

From source;

TableA:

noconcatenate

Load *

resident TempTable

where Year = 2010

;

Store TableA into SaveQVD_2010.qvd;

drop table TableA;

TableA:

noconcatenate

Load *

resident TempTable

where Year = 2011

;

Store TableA into SaveQVD_2011.qvd;

drop table TableA;

FinalTable:

noconcatenate

Load *

resident TempTable

where Year > 2011;

drop table TempTable;

----

End result is a table that only contains 2012-2015; why have more data in your data model if you do not want to use it anyway.

I assume it can be done more cool / nice, but this should work.

Re: Hide years data.

maxgro — Tue, 24 Feb 2015 22:51:31 GMT

filter the data (years) when you load from the database

with a where clause in the sql section of the load script

Re: Hide years data.

oknotsen — Tue, 24 Feb 2015 22:59:40 GMT

Wouldn't that require to go to your source multiple times? Once for 2010, once for 2011 and once for the rest?

In my code example he will go to the source once and do the "where" part in QlikView which asks less time of your source and probably is faster.

Re: Hide years data.

maxgro — Tue, 24 Feb 2015 23:15:04 GMT

from what I understand he has data in a database from 2010 to 2015 and he wants to show only last 3 years in dashboard (from 2012); so I prefer to read from the db only the years I need (2012-2015) in the dashboard

pseudo code could be

for year in 2012..2015

$(year):

sql select

from database

where yearfield = $(year);

store $(year) into saveqvd_$(year).qvd (qvd);

drop table $(year);

next;

Re: Hide years data.

mukesh24 — Wed, 25 Feb 2015 09:10:15 GMT

Hi,

When u r loading QVD into dashboard simply filter data on Year.

Re: Hide years data.

oknotsen — Wed, 25 Feb 2015 09:15:43 GMT

He is not loading from QVD, he loading from Database.

Even if he was loading from QVD and does not want 2010 and 2011 in his application, filtering on it is not a structural solution as filters get cleared and the data is still in the system.

Re: Hide years data.

mukesh24 — Wed, 25 Feb 2015 09:22:48 GMT

Hi Onno,

In Finance module u need whole data for calculation purpose.

For example Open item calculation.

Regards,

Mukesh Chaudhari

Re: Hide years data.

oknotsen — Wed, 25 Feb 2015 09:34:20 GMT

That might be in your situation, but San Reddy made clear he only wants 2012-2015 data in his application.

In other words:

What you are saying might be true for you, but is irrelevant for this specific situation.

Re: Hide years data.

mukesh24 — Wed, 25 Feb 2015 09:56:33 GMT

Hi Onno,

what u r saying might be right but as per his sentence he did not want Data in Dashboard.

Re: Hide years data.

Anonymous — Wed, 25 Feb 2015 09:58:15 GMT

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

Re: Hide years data.

oknotsen — Wed, 25 Feb 2015 10:01:08 GMT

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

Re: Hide years data.

Anonymous — Wed, 25 Feb 2015 10:10:45 GMT

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