Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Year Wise Qvd creation from Source Data

cancel
Showing results for 
Search instead for 
Did you mean: 
its_anandrjs

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

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.

IamBack
Champion
Champion

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

0 Likes
sakamsureshbabu
Creator
Creator

very nice A

0 Likes
Not applicable

Great, thanks for this

0 Likes
Not applicable

Thank you Anand..It is really helpful.

0 Likes
Colin-Albert

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.

0 Likes
Not applicable

Thank you very much for provide this.

0 Likes
cesaraccardi
Specialist
Specialist

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?

0 Likes
its_anandrjs

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

IamBack
Champion
Champion

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

0 Likes
Version history
Last update:
‎2014-12-10 08:47 AM
Updated by: