Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Year Wise Qvd creation from Source Data

its_anandrjs
Not applicable

Year Wise Qvd creation from Source Data


Source:

LOAD * From Source;


YearCount:
LOAD Year as TotYr Resident Source;

LET vYearMin = Peek('TotYr',0,'YearCount');
LET vYearMax = Peek('TotYr',-1,'YearCount');


Drop Table YearCount;

Drop Table Source;


FOR vTableName = $(vYearMin) to $(vYearMax)
//////////
NoConcatenate
$(vTableName):


LOAD * From Source.qvd (Qvd) Where Year = $(vTableName);
STORE $(vTableName) into $(vTableName).qvd;

DROP Table $(vTableName);


NEXT

Comments
its_anandrjs
Not applicable

Or May be used Order By or Distinct in the table load but already used Peek function.

LOAD Year as TotYr Resident Source Order By Year asc;


LOAD DISTINCT Year as TotYr Resident Source Order By Year asc;


Any suggestion is appreciated.

SunilChauhan
Not applicable

thanks nice code.what is the use case scnarios for this.could you please give example.

sakamsureshbabu
Not applicable

very nice A

Not applicable

Great, thanks for this

Not applicable

Thank you Anand..It is really helpful.

colin_albert
Not applicable

You can use yearly QVD's when you have some apps that only need 1 or two years data, and others that need all data, or your app only needs the last 5 years data. This way your app can be set to only load the qvds that are required rather than all data.

Not applicable

Thank you very much for provide this.

cesaraccardi
Not applicable

Thanks for this Anand!

A quick question... in the example you have:

LET vYearMin = Peek('TotYr',0,'YearCount');
LET vYearMax = Peek('TotYr',-1,'YearCount');

If my understanding is correct, this code is setting variables vYearMin with the value of the first record and vYearMax with the value of the last record from the source respectively. So if that's the case one needs to make sure the column Year is sorted in an ascending way in the source for this to work properly, is that right?

its_anandrjs
Not applicable

Thanks Cesar,

Yes for that we need to use

LOAD Year as TotYr Resident Source Order By Year asc;

LOAD DISTINCT Year as TotYr Resident Source Order By Year asc;


Regards

Anand

SunilChauhan
Not applicable

Peek  is fetching first and last value in vYearMin and vYearMax

LET vYearMin = Peek('TotYr',0,'YearCount'); -----Min Year

LET vYearMax = Peek('TotYr',-1,'YearCount'); -----Max Year


TotYryear is the fieldname  where suppose values are as below


2000

2001

2002

2003

2004

...

,...

..

....

2014



then

LET vYearMin = Peek('TotYr',0,'YearCount'); -----2000 to variable vYearMin

LET vYearMax = Peek('TotYr',-1,'YearCount');-----2014 to variable vYearMax


if it is sorted in ascending order

and vice veresa if in opposite order

thats why before using peek first make sure sorting ascending and then use peek

Version history
Revision #:
1 of 1
Last update:
‎12-10-2014 08:47 AM
Updated by: